DMax and AutoNumber

[Guest]

I have a database that uses a project number as a key in two tables. The
project number is similar to 16217-001 where 16 is the project code, 217 is
the location and 001 refers to this being the first project for the location.
I am trying to create a code or expression that will give me the DMax of the
last three digits for each project and location.

Basically, if I have a second project for location 217 for a 16 project, I
want access to generate this for me.

Any help will be appreciated.

 

[Larry Daugherty]

Keep that last 3 digit component as a separate field. You can
DMAX(yourtableandfield)+1 and use that as the new value to store in
that field and to concatenate and display with the other components of
your "intelligent key".

HTH

 

[Larry Daugherty]

Oh, not to make too big a deal of it but the DMax()+1 paradigm creates
a *sequence* which is what most people want when they miss-apply the
Autonumber datatype.

An Autonumber datatype is a horse of a different color. It is a very
special purpose datatype that is intended only to generate unique long
integer values to serve as surrogate Primary Keys. They are *not*
guaranteed to be sequential and, in time and with lots of use, can
almost be guaranteed to be non-sequential.

HTH