AutoNumber

[Rpettis31]

Is there a way to start the autonumber, at a number other than one. I would
like to do is when the form is opened the box would have a preset such as
08and then a number such as 001.

08001 would be the first id. The 08 being the two digit year prefix.

Thanks

 

[Douglas J. Steele]

See what Allen Browne has at http://www.allenbrowne.com/ser-26.html for how
to start an Autonumber at a specific value.

That having been said, I'd advise very strongly against trying to give
meaning to your Autonumbers like that. Autonumber fields should never have
meaning assigned to them. In fact, it's usual not to even let the user see
them. You're also violating database normalization rules by trying to put
two pieces of information into a single field. Store them as two separate
fields (one for the year and one for the autonumber). You can concatenate
them together on forms and reports if you really need that number.

 

[Wayne-I-M]

Hi

I can see so many problems with thios that I wouldn't bother (an update
query would do it better) .....
but seeing as you asked......


SELECT Format([TableName]![DateField],"yy") &
IIf(Len([TableName]![AutoNumberField])=1,"00",IIf(Len([TableName]![AutoNumberField])=2,"0")) & [TableName]![AutoNumberField] AS NewName
FROM TableName;

This will work up to record 999 (after that you will have to alter the
formula - but quite simple)

Note that this is a text string not a number so you will need to add the
other columns to the query to be able to sort it.

Good luck

 

[KARL DEWEY]

Number do not have leading zeros, only text fields.
You can use the DMax function (you'll find info on it in your Access help
file) to return the maximum existing number, and then add one to it.
Use a macro or event to test the first two digits against the year and
restart the sequence if year is not the same.

 

[Rpettis31]

There is a date field I am just trying to make a unique identifier for each
form. I will just use the random numbers as a a straight form id #.

Thanks
Robert