Auto ID Number with multiple prefixes

[Guest]

I have a table where I am storing project data. Users enter data via a
Projects form. Up until now, the project ID did not have a standard format
and anything could be entered in this field (due to use by many different
groups). The database will now be exclusively used in our workgroup. There
will be four possible initial prefixes (examples are: ADF; ICS). After the
initial prefix I need the current date and a four digit autonumber. I am
trying to figure out how to account for more than one initial prefix
(ADF...ICS..etc.) and still keep the numbers "in order".
I need ideas!
Help!

 

[John Vinson]

I have a table where I am storing project data. Users enter data via a
Projects form. Up until now, the project ID did not have a standard format
and anything could be entered in this field (due to use by many different
groups). The database will now be exclusively used in our workgroup. There
will be four possible initial prefixes (examples are: ADF; ICS). After the
initial prefix I need the current date and a four digit autonumber. I am
trying to figure out how to account for more than one initial prefix
(ADF...ICS..etc.) and still keep the numbers "in order".
I need ideas!
Help!

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. Storing more than one chunk of information in a field is a
Very Bad Idea in general. You can do it - but you'll really be better
off treating the prefix and the date AS DATA, rather than as part of
the key. You can maintain an Integer as the third part of the key;
use the Form's Beforeinsert event to find the maximum value for this
combination of prefix and date, and increment it. An Autonumber
datatype will NOT work in the context you describe.

You can store the information as a three-field Primary Key and
concatenate the three pieces for display purposes if you wish.

John W. Vinson[MVP]

 

[Guest]

Hi

You could store the data as text (other than the autonumber). Sort the data
by the number the concencate the results - as and when or if needed

Or

You could create a table with a multiple primary key

Or

You could simple create and index in your table and have a combination of
the three fields as unique.

But

The main the you don't want to do is to have diffenent data forced into a
sinlge field - bad D Base design.

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do

 

[Guest]

What I ended up doing (and since I am not an "expert" be gentle) is this:

I created a combo box on the form for the user to select the project code
(list of the possible prefixes) and a field containing the current date. I
created another field whose control source concencates the code, date and
autonumber project ID.

An example of a valid prefix is "ADFALL". I would like the concencated
field to look like this:

ADFALL06092101 (prefix, yymmdd, ID (with preceeding zero if one digit)

I set the format on the date field to YYMMDD. Here is what I get:

ADFALL069211 (prefix, yymdd, ID (single digit))

How can I get the date and ID to format the way I want in this field?

 

[Guest]

Never mind...I am tired and losing it. This won't work...of course...since I
need it to keep up with the prefix data and set the project number based on
that. Keep in mind I am not an Access expert!

 

[Rick Brandt]

Never mind...I am tired and losing it. This won't work...of
course...since I need it to keep up with the prefix data and set the
project number based on that. Keep in mind I am not an Access expert!

You need three separate fields for prefix, record date, and record ID. Then
a fairly simple DMax() + 1 variant can be used in the BeforeUpdate event of
the form used for insertions.

If Me.NewRecord Then
Me!ID = Nz(DMax("ID", "TableName", "Prefix = '" & Me!Prefix & "'"), 0) +
1
End If

You then *display* a concatenation of all three fields to get the format you
want.