Autonumber field with semi-static text?

[hntsignif]

I have a field that is an autonumber that I need to output and store in the
following format:

PRO(Autonumber)-XX

The XX will change once a year to the next years last two digits. ie this
year will all be PRO(autonumber)-09 and next year will be PRO(Autonumber)-10.

Do I need to store in a separate field or can I make it store in the same
field with the text?

Thank you,
Holly

 

[Jeff Boyce]

Holly

Access Autonumbers are designed to serve as unique row identifiers. As
such, they are NOT guaranteed to be sequential and they are generally unfit
for human consumption.

If you need a sequence number that you control, add a sequence number field
to the table and use one of the variations on the (MAX of previous values +
1) functions you can learn more about at mvps.org/access (search online for
"Custom Autonumber").

Instead of storing a constant "PRO" and a sequence number and a two-digit
representation of the year, consider using a query to generate a
concatenation of these on the fly ... this gives you a way to display
PRO12345-09 without having to store it.

By the way, if your record has a date/time field, you can get the XX value
in your query from that field.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[hntsignif]

I don't think I said this correctly.

Esentially what I need is a stored field that advances by 1 each time that
always starts PRO and ends in dash year number.

It needs to be stored because I don't want it changing once it is assigned
and some of them run multiple years. All documents, pictures, test data,
etc. will reference back to this initial sequence so it cannot change once
assigned.

I know how to put an input mask and format on it so that it shows correctly,
but when you change the year, it changes on all entries. I need it to store
with the current information not just look right.

Holly

__________________________________

 

[Jeff Boyce]

Holly

It is generally considered poor design to have a field that holds more than
one fact. What you are proposing is to stuff three separate facts into a
single field. Just say "no".

Instead, don't store the "PRO" at all. You can use formatting (or a query)
to add this in as needed.

And don't store the YY part if you already have a date field in the record.
You can use formalling (or a query) to get the YY part of the date.

Use a single field for the sequence number -- check on-line for "custom
autonumber" for a way to increment the sequence number.

Keeping the users from changing pieces of this is another reason why you'd
use a form to display the information rather than giving the users direct
access to the tables...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Sorry, fast fingers, slow brain.

That should have read "formatting", not "formalling"...

Regards

Jeff Boyce
Microsoft Office/Access MVP