alphanumeric ID numbers

  • Thread starter Thread starter Stiruchi
  • Start date Start date
S

Stiruchi

Hello,

Is it possible to have the ID field (autonumber?) in alphanumeric
sequential numbers. (e.g.
DMC001,DMC002,DMC003...........DMC120.....etc). The reason for this is
that I have two parallel databases running and want to be able to trace
the source of the data from the reports generated. (e.g. DMC001 from
generated from database 1 and PMT001 from Database 2).

Thanks in advance

Stiruchi
 
This is a fairly common question. You can construct a routine that will get
the next number for you, but don't confuse this with AutoNumbers. Never do
anything with them except use them for keys.

Using your example of three letters always being DCM followed by 3 numbers:

strHighNum = Right(Nz(DMax("[MyID]", "MyTableName"),"000"),3)
strHighNum = Format(Cint(strHighNum) + 1, "000")
strNewNum = "DCM" & strHighNum
 
Yes but the real question is should you? In a normalized database, each
field should represent one discrete piece of information. This solves and
prevents all sorts of problems - not the least of which is implementation. A
complex value like this is difficult to maintain and less useful for
reporting.

You really need to consider using two fields - one representing the Source
of the data and the second field as an id. Then create a unique index that
includes both fields. You can easily concatonate the fields for reports or
display purposes.
 
Sandra,

Thanks and noted suggestion. I can see that with the 2 field sugestion
I should also be able to merge the records from the two databases at
some stage if I wanted to.

Stiruchi
 
Back
Top