Autonumbering

[sosborne]

Is there any way to format an autonumbering field to show leading zeros?

I want the field to show 0001 instead of 1.


Thanks

Sam

[mattj]

You are confusing what an autonumber is intended for. The autonumber data type is for uniquely identifying a record. It cannot be relied upon to even be sequential, and is not for "public consumption".

You should have a separate field with an integer data type, from which you extract the highest number and add one to it. Then format it as you please for display ina form, query, or report.

HTH
Matt

[X-BRichard-X]

You can use a calculated query to work in conjunction with your primary key field to create the results you are seeking.

Let's say that you primary key field name is ID1. What you could do is to create a calculated field called, let's say, IDx within a query with the following syntax:

IDx: "000" & [ID1]

The result is that 3 leading zeros will be added to each primary key number for each record in your table in your IDx field which can then be used as needed.

Now, if you want the total digit length to be 4 places, then you will have to craft a module function that will parse out the primary key number based on the number of digits it has and reflect the appropriate leading zero total per the primary key number.

[DarkSprout]

Call This Function, Will Return A String With Your Numeric And Leading Zeros

Public Function LeadingZero(nValue As Long, Optional MaxChars As Integer) As String
'// will return String with zeros prefixed to a max length
MaxChars = IIf(MaxChars = 0, 6, MaxChars) + 1 ' default MaxChars to 6 if missing
LeadingZero = String(MaxChars - Len(str(nValue)), "0") & nValue
End Function



eg.
In The Immmediate Window
?LeadingZero(123)
Will return:
000123