The business wish to have a unique identifier which is month, year, nnn where
n starts at zero each month and is sequential as each quote comes in until
the end of the month. Whereby the sequential numbers begin at zero again.

For example the 43rd quote this month would have a reference number of
12/09/043

To avoid duplication they wish Access to prompt with the new reference
number when they access the new quote form.

Can this be done?

Put your identifier in a column with a unique index.
Then from the Date and DatePart functions (look in HELP) you get the current
year and month. The you do a DMAX function (look in HELP) on this table to
get the highest current value then add 1 and immediately save the table row
(otherwise someone else may grab the number before you save it).
I don't see how your prompts avoid duplication. Perhaps management never
heard of a 'unique index'.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"JTS" wrote:

> The business wish to have a unique identifier which is month, year, nnn where
> n starts at zero each month and is sequential as each quote comes in until
> the end of the month. Whereby the sequential numbers begin at zero again.
>
> For example the 43rd quote this month would have a reference number of
> 12/09/043
>
> To avoid duplication they wish Access to prompt with the new reference
> number when they access the new quote form.
>
> Can this be done?