In access, is it possible to autonumber with a prefix? ie ABC0001

[Guest]

I use an incremented reference, always starting with the same three letters,
can autonumber be set so these letters are always prefixing the autonumber
(as opposed to the user having to check last ref, add one and type it in ???

 

[Rick B]

If the first three letters are ALWAYS the same, then don't worry about it.
Just store the numbers. In your reports, forms, and queries, you can use
code to add on the prefix, but why store it if it will always be the same?

 

[Al Camp]

Coz,
No, the autonumber type is a long integer. ABC0001 would be a text field. Use an
autonumber to generate the 1 or 2, or 3, etc... and then append the ABC and the
appropriate number of zeros to your autonumber in another calculated text control.

This will get a bit messy though... as the length of the autonumber will grow over
time, and the correct number of 0's to be cocatenated to it will vary.
Also, "pad" enough 0's to cover larger numbers. What happens after ABC9999 occurs?

Also, that concatenated string does not have to be saved to a field. You can always
calculate (just display) it on the fly in any form or report or query.
Using my format of ABC-132, a calculated text control on your form with...
= "ABC-" & [YourAutoFieldName]
will always display the correct value/format you want.

I'd suggest using ABC-1 or ABC-3145 type format, then the "ABC-" can be appended
directly to the value of the auto field.

 

[Guest]

Al

I tried that - created an new autonumber field on the table (called New Ref)
then entered = "ABC" & [New Ref] into the form - it displayed the ABC, but
didn't add in the autonumber (BTW the form is set to Data Entry = Yes and New
Ref is a brand new field)

MTIA

Coz,
No, the autonumber type is a long integer. ABC0001 would be a text field. Use an
autonumber to generate the 1 or 2, or 3, etc... and then append the ABC and the
appropriate number of zeros to your autonumber in another calculated text control.

This will get a bit messy though... as the length of the autonumber will grow over
time, and the correct number of 0's to be cocatenated to it will vary.
Also, "pad" enough 0's to cover larger numbers. What happens after ABC9999 occurs?

Also, that concatenated string does not have to be saved to a field. You can always
calculate (just display) it on the fly in any form or report or query.
Using my format of ABC-132, a calculated text control on your form with...
= "ABC-" & [YourAutoFieldName]
will always display the correct value/format you want.

I'd suggest using ABC-1 or ABC-3145 type format, then the "ABC-" can be appended
directly to the value of the auto field.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I use an incremented reference, always starting with the same three letters,
can autonumber be set so these letters are always prefixing the autonumber
(as opposed to the user having to check last ref, add one and type it in ???

 

[Larry Linson]

Al

I tried that - created an new autonumber field on the table (called New
Ref)
then entered = "ABC" & [New Ref] into the form - it displayed the ABC, but
didn't add in the autonumber (BTW the form is set to Data Entry = Yes and
New
Ref is a brand new field)

The Autonumber value won't be set until the Record is written, so you would
not see it in a Data Entry form. It's not good practice to display
AutoNumber Fields, in any case. They are not necessarily the "monotonically
increasing" values that many expect -- they are intended to be _unique_, and
used for internal purposes in the database (as "surrogate keys", linking
related tables, for example).

There have been quite a lot of postings of code to generate the
monotonically increasing numbers, with no gaps in the sequence, that "green
eyeshade accounting types" and others expect. Most of them use the DMAX
domain aggregate function. Perhaps Googling the archives at
http://groups.google.com using "sequential" and "DMAX" will turn up a useful
solution.

Larry Linson
Microsoft Access MVP