leading zeroes in alphanumeric id number

[ahalf0rd]

Hi,

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.

cheers

Andy

[Stewart Ross]

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:

Prefixed ID: "CP" & Format([ID field name], "00000")

-Stewart

[missinglinq]

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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDField = "CP0001"
  Else
   Me.IDField = "CP" & Format(DMax("val(Right([MyID],5))", "YourTable") + 1, "00000")
   End If
 End If
 End Sub

Where:

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)>