Customising an autonumber

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Lees - 18 Jul 2006 13:30 GMT

I am implementing an access database and need an autonumber field to
automatically fill in when a new record is created. I need the field to be in
the following format...

QN then 5 digit number starting from 06700 or around there then the last two
digits of the year on the end.

EG

QN0676306

Can any1 help me with this?

Cheers

Danny

Reply to this Message

Rick Brandt - 18 Jul 2006 13:50 GMT

> I am implementing an access database and need an autonumber field to
> automatically fill in when a new record is created. I need the field
[quoted text clipped - 8 lines]
>
> Can any1 help me with this?

Do not use AutoNumber any time you care about the values. Use a combination
of two fields. One a Long Integer and the other a DateTime. Since the
prefix "QN" never changes this does not need to be stored, but can simply be
displayed via formatting. Example with a field named ID you could use the
following code in the BeforeUpdate event of the form used to add records.

If Me.NewRecord Then
Me!ID = Nz(DMax("ID", "TableName"), 6699) + 1
End If

You would also have a DateField [CreateDate] that defaults to Now(). Then
you would have a TextBox on your form to display the combined value with a
ControlSource of...

="QN" & Format(ID, "00000") & Format(CreateDate,"YY")

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message