Validation in Table Field

[MiAs]

Table field (primary key) data type:Number
How can I validate / symbol as in 05/01 representing year and 1st record, etc.
Many thanks in anticipation.

 

[Pat Hartman]

You can't have a / in a numeric field. You appear to be attempting to create a primary key with "meaning". An autonumber will work better and require no work. If you want to keep track of the year something happened, just store the date the record was created and pull out the year any time you want it by using the Year(), DatePart(), or Format() function.

To create a two-part key, you need to use two separate fields. Put the year in one field and the sequence number in the other. Search for the hundreds of threads on the topic and choose one that suggests DMax() to find the previous sequence number for a particular value in field1. Don't forget, you need to write your own code to manage duplicate sequence numbers if you intend to do this in a multi-user database.

For display purposes, you can concatenate the two fields with a / between them.

PS, For those of you thinking you can just use the Left() function to pull off the two leftmost positions of a numeric field, think again. String functions are unreliable when used for numeric fields. At least that's what help says and I believe them in this case.

 

[statsman]

Change the "type" of field from number to date.

Use the datediff function to figure out the number of days.