Appending auto increment numbers on a given root

[thorben.grosser]

Hello Newsgroup,

as I am building an archive database, I got to give every folder a
single ID which is made of different characters, eg: 1-2-568-5468.
The fixed part 1-2-568- gives information about the folders location
(rack, building...) so is determined by the user input.
The last four digits are an automatically assigned number ranging from
0000-9999.

How do I create such an assignment if I want not only an automatically
incrementing number, but it should be a new series for every fixed
part (1-2-568-0001, 1-2-847-0001, 2-2-441-0001 instead of
1-2-568-0001, 1-2-847-0002, 2-2-441-0003...) in MS Access,

thanks in advance
Thorben Grosser

 

[Stefan Hoffmann]

hi Thorben,

as I am building an archive database, I got to give every folder a
single ID which is made of different characters, eg: 1-2-568-5468.
The fixed part 1-2-568- gives information about the folders location
(rack, building...) so is determined by the user input.

This is a wrong table layout as the "fixed part" contains meanigful
information. So add fields for "rack, building..." and your four digit
number.

You can than easily use

Nz(DMax("FourDigit", _
"table", _
"rack = .. AND building = .. AND .."),0) + 1

to determine your next number.

mfg
--> stefan <--