How do I create an autonumber that also includes letter and year?

D

Dedren

It is a case number of the format: F05-123
-F stands for the type of case
-05 is the year the case was opened
-123 is just a number to tell them different.
I want the entire alphanumeric serial to be autogenerated based on
information entered in the database using a form.

I have read some similar questions but nothing tackling something this
complex.
 
S

Sprinks

Dedren,

The simplest way to implement this is to store the pieces in separate fields
and concatenate them for display to your users.

If you don't need to renumber starting with one at the beginning of each
year, and you don't mind a missing case number if a case is deleted, you
could use one numeric field for the case type and an autonumber primary key
for the case number. Display the composite case number in an unbound
textbox, and similarly build it as a calculated field in a query for use on
your reports.

Assuming that the text corresponding to the selected file type ID is in the
2nd column, and that you have a DateOpened field that places the case in a
particular year, the expression for the concatenated filenumber would be:

=[YourComboBox].Column(1) & format([DateOpened],"yy") & Trim([CaseNumber])

If you wish to renumber each year, you can use DMax to determine the highest
casenumber for the current year, and then add one.

Hope that helps.
Sprinks
 
A

aaron.kempf

yeah

you dont WANT an autonumber that contains number and year

it just makes things slower to change from numeric to text.

store it in a 2nd field as text; and put a unique constraint on it
 
Top