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.

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

"darnett" <(E-Mail Removed)> wrote in message
news:41A568C7-EE11-4851-BAA3-(E-Mail Removed)...
> 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.

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
--
-Larry-
--

"Larry Daugherty" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> 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-
> --
>
> "darnett" <(E-Mail Removed)> wrote in message
> news:41A568C7-EE11-4851-BAA3-(E-Mail Removed)...
> > 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.
>
>