<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2719.2200" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=171344012-15052003><FONT color=#0000ff
size=2>Virginia,</FONT></SPAN></DIV>
<DIV><SPAN class=171344012-15052003><FONT color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=171344012-15052003><FONT color=#0000ff size=2>Make life easier
for yourself and keep each of these items individual fields. The
arithmetic is easier done on strictly numeric fields. You can concatenate
them together when you need to display them.</FONT></SPAN></DIV>
<DIV><SPAN class=171344012-15052003><FONT color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=171344012-15052003><FONT color=#0000ff size=2>I have a customer
I could kill because they came to me AFTER printing a million new forms and
asking me to adjust the database to accept the new serial numbers on the
forms. Of course the new serial numbers do not fall into either proper
numeric or alphabetic sorting sequence with the old serial numbers. So I
am having to do some of the same things you are wanting to
do.</FONT></SPAN></DIV>
<DIV> </DIV><!-- Converted from text/rtf format -->
<P><SPAN lang=en-us><B><FONT color=#0000ff>Charles Wortz</FONT></B></SPAN>
<BR><SPAN lang=en-us><FONT color=#0000ff>Software Development
Division</FONT></SPAN> <BR><SPAN lang=en-us><FONT color=#0000ff>Texas Education
Agency</FONT></SPAN> <BR><SPAN lang=en-us><FONT color=#0000ff>1701 N. Congress
Ave</FONT></SPAN> <BR><SPAN lang=en-us><FONT color=#0000ff>Austin, TX
78701-1494</FONT></SPAN> <BR><SPAN lang=en-us><FONT
color=#0000ff>512-463-9493</FONT></SPAN> <BR><SPAN lang=en-us><FONT
color=#0000ff>CWortz@tea.state.tx.us</FONT></SPAN> </P>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Hollis,Virginia
[mailto:HollisVJ@pgdp.usec.com] <BR><B>Sent:</B> Thursday 2003 May 15
07:33<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></SPAN></FONT></BODY></HTML>