Field format

[szag via AccessMonster.com]

I need to have a project number that forces ensures that the field has 5
parts:

1. Always the letter C
2. Last two years of Year
3. 3 digit number (sequential)
4. "-"
5.2 digit sequential (usually just 00)

So the first Project in 2010 will be : C10000-00, 2nd will be C10001-00, 3rd
C10002-00 and so on

How do I enforce this in the field - both in the table and in the form...

Thanks!

 

[John W. Vinson]

I need to have a project number that forces ensures that the field has 5
parts:

1. Always the letter C
2. Last two years of Year
3. 3 digit number (sequential)
4. "-"
5.2 digit sequential (usually just 00)

So the first Project in 2010 will be : C10000-00, 2nd will be C10001-00, 3rd
C10002-00 and so on

How do I enforce this in the field - both in the table and in the form...

Thanks!

This is called an "Intelligent Key" - and that is unfortunately not a
compliment!

Two basic principles of relational database design are that fields should be
"atomic" - containing only one fact; and that no field should depend on any
other field (except for the primary key). This field violates both. Another
good rule is that you shouldn't store repeated data - the C and the - are
there in every record, just wasting space.

I would really construct this project number as needed from multiple pieces.
You could use a (concealed, hidden from the user) Autonumber as the project
ID, and have fields for ProjectDate, SeqNo, and SecondarySeqNo, with a unique
index on the three fields; you can construct your project number by
concatenating the pieces:

ProjectNo: "C" & Format([ProjectYear], "yy") & Format(SeqNo, "000") &
Format([SecondarySeqNo], "\-00")