[AccessD] Numbering Different Criteria

Virginia,

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".

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.

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.

John W. Colby
www.colbyconsulting.com
  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hollis,Virginia
  Sent: Thursday, May 15, 2003 8:33 AM
  To: 'accessd at databaseadvisors.com'
  Subject: [AccessD] Numbering Different Criteria


  I need to set up a way to assign sequential numbers based on different
criteria.

  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:

  AB1234-C001 (tracking number - section, sequential number)
  AB1234-C002
  AB1234-M001
  CD6789-E001
  CD6789-E002
  CD6789-M001

  The numbers also contain revisions and change letters. If I want the next
Civil number for document tracking number AB1234, it would be:

  AB1234-C003, Rev 0, Ch 0 (document number - section, sequential number,
revision, change)
  then AB1234-C003, Rev 0, Ch A
  or AB1234-C003, Rev 1, Ch 0
  AB1234-C003, Rev 1, Ch A

  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.

     ' Find the record that matches the control.
      Me.RecordsetClone.FindFirst "[DocID] = '" & Me![cboDocNum] & " AND
[Section] = " & Me![cboDocNum] & "'"

  Now I got to wondering how I would handle the Revision & Change Letter.
Any ideas how I can put all this together?

  Virginia
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030515/7b960a3b/attachment-0001.html>