Auto Fill field with 0's

M

MCW John

We use an ID field, 10 characters long, zero-filled. Example: 0000012345. In
Access, how do I define a field so it has a lenght of 10 and is zero-filled?
 
A

Arvin Meyer

MCW John said:
We use an ID field, 10 characters long, zero-filled. Example: 0000012345. In
Access, how do I define a field so it has a lenght of 10 and is
Click to expand...
zero-filled?

Just define it as a 10 character text field. You will need a function to
fill it, so paste this into a standard module:

Function LPad(strIn As String, Size As Integer) As String
LPad = String(Size - Len(strIn), "0") & strIn
End Function

Then use it in your form like this:

Sub txtID_AfterUpdate()
Me.txtID = LPad(Me.txtID,10)
End Sub

Where txtID is the name of the text box that you enter your ID and which is
bound to the ID field in the underlying table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
S

Sprinks

AFIAK, John, there is no way to define this format, but you can certainly use
the AfterUpdate event to pad the beginning with zeros. The trick is to add a
10-based number to the value in the field one power of ten larger than the
number of characters you want to keep, in your case:

YourTextBox = Right(YourTextbox + 10000000000#, 10)

Sprinks
 
G

Garret

MCW said:
We use an ID field, 10 characters long, zero-filled. Example: 0000012345. In
Access, how do I define a field so it has a lenght of 10 and is zero-filled?
Click to expand...

Right in the table design window, go to that field and look at the
Format property. In there, type 0000000000. From now on, if you type
1 in the field, it will show up as 0000000001.

Unless you wanted to do this through code, then do it Doug's way.
 
S

Sprinks

Doug,

So in John's case, he can set his Format property to "0000000000", and avoid
an event procedure.

Another arrow in the quiver; thanks.

Sprinks
 
Top