<!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=093220113-15052003><FONT face=Arial color=#0000ff
size=2>Virginia,</FONT></SPAN></DIV>
<DIV><SPAN class=093220113-15052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093220113-15052003><FONT face=Arial color=#0000ff size=2>What
you are describing are simply properties of a document, and as such need their
own field in the document table. Once you have done that any of the
various pieces can be adjusted independently of any other. Do NOT try to
deal with this as a single entity. Assign the pieces, then pull the pieces
out and append them all together in a formatted string for the "document
number".</FONT></SPAN></DIV>
<DIV><SPAN class=093220113-15052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093220113-15052003><FONT face=Arial color=#0000ff size=2>As far
as answering the question "what is the next number in the sequence" you need an
algorithm to discuss how the pieces "roll over" into the next
field.</FONT></SPAN></DIV>
<DIV><SPAN class=093220113-15052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093220113-15052003><FONT face=Arial color=#0000ff size=2>With
no intimate knowledge of the numbering scheme, I would guess the first two
characters mean something, i.e. AB has a meaning, CD has a meaning etc.
Next the numeric portion. Of course you have defined the single character
in the middle and everything after that. Completely breaking it down at
least allows you to go to the people who thought up this scheme and discuss this
thing a piece at a time. Get inside that pea brain and you will be
trapped... uh... be able to figure out where to go next.</FONT></SPAN></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>John W. Colby<BR>www.colbyconsulting.com</FONT> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of
</B>Hollis,Virginia<BR><B>Sent:</B> Thursday, May 15, 2003 8:33
AM<BR><B>To:</B> 'accessd@databaseadvisors.com'<BR><B>Subject:</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>