Use and auto-entered serial number field to auto-enter a serial number.
Anytime you need to display that value on a layout where you need the leading zeroes, use a calculation field to add in the leading zeroes:
Right ( "000" & SerialNumberField , 4 )
But do you really mean that you want a string that is 400 characters in length or an ID number with a maximum value of 400?
Thank you so much, Phil, for your kind response!
The only thing though, is that I don't think the Layout approach will work. The users want to have two ID fields, one with a 4 character maximum, and the other with a 3 character maximum and then a final ID that would add the two together. So, the first patient, would have the first ID field as 0001 and the second ID field as 001 and their final ID field as 0001001. I hope what I'm describing makes sense... thank you so much again!
What you describe now does not match your original post.
Can you explain why they need two ID fields to be combined like this?
You may need such a field to support existing systems, but save yourself a lot of trouble by not using any of these ID's as the primary key for linking records in relationships. Keep such a field as a data field in the patient table so you can display it where needed but just don't use it as a match field in your main relationships.
What I described was for a single ID, you can use the same method twice to get two ID's, one with up to 3 leading zeroes and one with up to two. But if the first patient is to be identified as: 0001001, what value is assigned to the second? 0001002 or 0002002?
Sorry, Phil, for not describing it better in my first post.
I walked into the project and that was what they had decided, they need a primary key that's made up of appending these two other ID fields (one which has a max of four numbers and the other three numbers, both being auto-generated numbers.
The value to the second person would be 0002002. This 0002002 number would be the primary key that other tables would link to.
Is that possible?
It's very weird but quite possible, but I strongly, strongly advise that this NOT be the actual primary key in your database design. Put it where they can find and use it so it looks like it's the primary key and use a simple auto-entered serial number as the real primary key.
Take what I posted before and do it twice:
Right ( "000" & SerialNumberField , 4 ) & Right ( "00" & SerialNumberField , 3 )
But you should ask them what value should be shown for Patient number 1000 as this numbering scheme will fail with the 1000th record.
Why is it bad to use this number that's generated by appending two other numbers as a primary key?
The ideal primary key should be:
- Never ever changed once a value is assigned to a new record
- devoid of any "encoded meaning"
- As simple and "bullet proof" as possible
This ID fails requirement number 4 and may fail requirement number 3. (We still don't know why they want such an odd format...)
As I have already pointed out, the proposed numbering system has a built in failure point once you have more than 999 records in your table.
A common solution that keeps users happy is to put such ID values in field in the parent table so that it can be shown on any layout where data from the parent table can be shown--enabling users to use this field to search out records and for sorting records in reports, but not using it as a Primary key for use in relationships. Then, should a situation develop where the value or its format needs to be changed, changing the value is a simple data entry task that does not affect the function of your relationships.