How do I make a multi-part autonumber?

[Guest]

How do I make a multi-part autonumber or similar incrementing unique value?
Example:
F05-001
F05-002

The "F" is the type of case it is, which comes from another value in the
same record
The "05" is the year it was opened, which comes from another value in the
same record
The "-001" is the value that increments per record

We have been trying for a week now to think of something best we could come
up with was making seperate tables for each part of the number that would do
a sort of 'if' statement:
IF (casetype=M) PUT M
IF (casedateyear=2005) PUT 05
THEN increment (ending+1=ending)

I would appreciate any bit of advice anyone could give me cause I am stumped.

 

[Douglas J Steele]

You don't.

"Smart keys" (the term's slightly derogatory) aren't recommended. You're
trying to store 3 pieces of information: use 3 separate fields. You can
create a query that concatenates them together for display purposes if you
really need to, and use the query wherever you would otherwise have used the
table, but storing multiple pieces of information in a single field is
actually a violation of relational database rules.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How do I make a multi-part autonumber or similar incrementing unique value?
Example:
F05-001
F05-002

The "F" is the type of case it is, which comes from another value in the
same record
The "05" is the year it was opened, which comes from another value in the
same record
The "-001" is the value that increments per record

We have been trying for a week now to think of something best we could come
up with was making seperate tables for each part of the number that would do
a sort of 'if' statement:
IF (casetype=M) PUT M
IF (casedateyear=2005) PUT 05
THEN increment (ending+1=ending)

I would appreciate any bit of advice anyone could give me cause I am

stumped.

 

[peregenem]

"Smart keys" (the term's slightly derogatory) aren't recommended.

"Intelligent keys" (the non-pejorative equivalent), such as the ISBN,
are recommended.

 

[Joseph Meehan]

How do I make a multi-part autonumber or similar incrementing unique
value? Example:

....

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.

 

[Guest]

Is there any other way to generate a unique alphanumeric number automatically?

 

[chriske911]

How do I make a multi-part autonumber or similar incrementing unique value?

Example:
F05-001
F05-002

The "F" is the type of case it is, which comes from another value in the
same record
The "05" is the year it was opened, which comes from another value in the
same record
The "-001" is the value that increments per record

We have been trying for a week now to think of something best we could come
up with was making seperate tables for each part of the number that would do
a sort of 'if' statement:
IF (casetype=M) PUT M
IF (casedateyear=2005) PUT 05
THEN increment (ending+1=ending)

I would appreciate any bit of advice anyone could give me cause I am stumped.

easiest way is to write a module and let it generate a new number in
the before update event of the record

you can lookup the current max number from the table by using dmax or
by keeping a follow number in a separate table and update it every time
you generate a new number

then use all kind of formats to concatenate them to a unique number

grtz