Why do you need the letters and leading zeroes? This complicates your design to very little benefit. A simply auto-entered serial number is all that you really need. What happens to your identifier if a name is later change?
If you must have this format, say to support existing practices. Set up your real ID field as an auto-entered serial number and use this calculation field calculation to produce the visible identifier from the hidden simple serial number:
Left ( LastName ; 2 ) & Left ( FirstName ; 1 ) & Right ( "000" & SerialNumberField ; 4 )
Thank You PhilModJunk ! That will work ! On the current program I am using, when a last name changes I just have to create a new record for the new last name. Your suggestion works great and I think I like it better !! Thanks bunches !
When you link tables in relationships, use just the serialnumber field, not the calculation field that adds additional text and formatting to it. That way, name and format changes do not break the links to related data. Use the field with added text and leading zeroes as your "public" identifier such as you might print on a label or a report.
Thanks ! That would be a good way to do it too. Darn, now I have to go think about it for awhile and see which way would work best for what I'm doing. Thanks bunches !