Unique field

[Big Steve]

As a newer user to MS Access 2003 I have run into a few problems. I have
dabbled a bit and figured out some of my own answers by accident and reading,
however, I am trying to create a unique field on a form for a user. I have
the field set up to input some characters automatically but the user would
have to put in the last two digits of the year and numeric month and a three
digit job number. The format appears like this:

XXXX-0704-001 (<- YYMM, first job)

Is there a way to have access automatically detect the year and month and
put jobs in successive order as new records are created.

I currently have the input mask set to "XXXX-"0000\-000;0;

Thanks for any help in advance.

 

[Douglas J. Steele]

You appear to be trying to create a so-called "intelligent key", and the
term is not used in a complimentary fashion. <g>

Realistically, you're trying to store multiple pieces of information in a
single field, and that's a violation of database normalization principles.
Use multiple fields (each piece of information in its own field), and
concatenate them for display purposes if that's necessary. To do that, you'd
create a query that has a computed field that concatenates the other pieces
of information, and then use the query wherever you would otherwise have
used the table.

 

[Big Steve]

Thank you doug. That being said. My option is to create a three windows?

I have the First field already setup for the input mask (which was easy
enough)

I guess the question would now be how do I populate the:
second field (YYMM), and
third (three digit Job Number for the month)

 

[Douglas J. Steele]

What is the 2nd field supposed to represent: the year and month the record
was created, or something else? If the former, set its DefaultValue to Date,
and its format to yymm. If something else, set it to that date.

For the 3rd field, you can put logic in the form's BeforeInsert event to
determine the largest value already used for the job number, and assign one
more than that to the field before it's stored in the table.