<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff size=2>We
recommend to separate the main code like:</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff size=2>XXNNNN
the fisrt part like Alphanumeric Field with 6 positions</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2>X only 1 position to
identificate Engineering Section</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2>NNN three digits from and integer value converted
to String with filled 0 on the left</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2>NN revision
number</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2>NN Change
number</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff size=2>With
this in the future you couldn't have any problems making changes on the main
database or making any query you obtain a very adaptable
query.</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff size=2>Good
luck</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2>Juan</FONT></SPAN></DIV>
<DIV><SPAN class=900260813-15052003><FONT face=Arial color=#0000ff
size=2>Mastercafe</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Mensaje original-----<BR><B>De:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>En nombre de
</B>Hollis,Virginia<BR><B>Enviado el:</B> jueves, 15 de mayo de 2003
14:33<BR><B>Para:</B> 'accessd@databaseadvisors.com'<BR><B>Asunto:</B>
[AccessD] Numbering Different Criteria<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>I need to set up a
way to assign sequential numbers based on different
criteria.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>The first part of
the number is a document number, it can be either AB1234 or CD6789, the
next part is for the Engineering section (C for Civil, M-Mechanical,
E-Electrical). So the numbering scheme would be:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>AB1234-C001
(tracking number - section, sequential number)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>AB1234-C002</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>AB1234-M001</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>CD6789-E001</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003><FONT face=Arial
size=2><SPAN
class=800250212-15052003>CD6789-E002</SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003><FONT face=Arial
size=2><SPAN class=800250212-15052003><FONT face=Arial size=2><SPAN
class=800250212-15052003>CD6789-M001</SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>The numbers also
contain revisions and change letters. I</SPAN></FONT><FONT face=Arial
size=2><SPAN class=800250212-15052003>f I want the next Civil number for
document tracking number AB1234, it would be:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>AB1234-C003, Rev
0, Ch 0 (document number - section, sequential number, revision,
change)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>then AB1234-C003,
Rev 0, Ch A</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>or AB1234-C003,
Rev 1, Ch 0</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>AB1234-C003, Rev
1, Ch A</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>I thought if I
could have the form select the tracking number & the section, I could use
a query to designate what the next sequential number would be based on the
tracking number & the section. Then I couldn't get the two different
criteria to work together. </SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003> '
Find the record that matches the control.<BR>
Me.RecordsetClone.FindFirst "[DocID] = '" & Me![cboDocNum] & " AND
[Section] = " & Me![cboDocNum] & "'"</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>Now I got to
wondering how I would handle the Revision & Change Letter. Any ideas how I
can put all this together?</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>Virginia<BR></DIV></BLOCKQUOTE></SPAN></FONT></BODY></HTML>