By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

leading zeroes in alphanumeric id number

P: 2

I have a text field as a unique id number for my database. It is in the format
CP????? where the first record will be CP00001, CP00002 etc. Unfortunately I cannot get my table or form to include the leading zeros so any help would be appreciated. I am not a programmer just a biologist trying to build a robust database for my work.


Mar 18 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
Hi. A text field is not the right one to use if you wish to have leading zeros displayed automatically. Does the CP prefix refer to another object? Is it always the same value or will it change according to other aspects of the data? If the answer to either of these is yes then you have a compound key - a multi-component key - which should be stored in two fields, not one.

The numeric component should be a number (a long integer or an autonumber field). The format property of a number field can be set to 00000 to ensure that values are displayed with 5 digits, any unused ones appearing as leading zeros.

Although autonumber fields have their drawbacks they do return unique values in ascending sequence. If you don't require a compound key you could keep the key field as a simple autonumber and use a query on your table to display its value with the prefix. in the query editor you could add the following calculated field:
Expand|Select|Wrap|Line Numbers
  1. Prefixed ID: "CP" & Format([ID field name], "00000")
Mar 18 '08 #2

Expert 2.5K+
P: 3,532
Someone, John Vinson I think, once said "Autonumbers are not fit for human consumption!" and truer words have never been spoken! There are simply too many things that can cause gaps in Autonumbers, other than deleted records! And things such as corruption can cause procedures to be used that will change the autonumbers.

For ID numbers, especially where they will be referred to/referenced in hard copy documents (yes, people, some people do actually have non-PC generated documents) you really should use code to generate auto-incrementing numbers. It's not that difficult:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  If Me.NewRecord Then
  3.   If RecordsetClone.RecordCount = 0 Then
  4.    Me.IDField = "CP0001"
  5.   Else
  6.    Me.IDField = "CP" & Format(DMax("val(Right([MyID],5))", "YourTable") + 1, "00000")
  7.    End If
  8.  End If
  9.  End Sub

IDField is the textbox on your form holding the "number."
MyID is the field on your table holding the "number."
MyID is of Datatype Text.
YourTable is the name of your underlying table.

Welcome to TheScripts!

Linq ;0)>
Mar 18 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.