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

[Guest]

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.

 

[Guest]

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

 

[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

 

[Joseph Meehan]

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.

As noted, you use two fields and combine them when you display the
results.

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.