Leading with 0's in a table.

  • Thread starter Thread starter Debra Ann
  • Start date Start date
D

Debra Ann

MS Access 2003:

I have a number field in a table that must be nine characters and fill with
0's in the front of the number depending on how big it is. How do I mask it
so that if the person types 4236 then it shows up as 000004236?

Thanks
 
Debra Ann

Let's make sure we're talking about the same thing, first.

Numbers are things you add/subtract/multiply/divide. Any zeros that come at
the beginning are meaningless (that is, "000004236" = "4236").

If you'll be "doing math", then you have numbers. You can change the format
to change how they appear.

But if this is actually more of a "code", and "doing math" is not needed,
then change it to a text field. While you still will have the option of
entering "4236" and displaying "000004236" (via formatting), you could also
rig your form to convert anything less than nine characters to be
zero-padded when stored. (NOTE: you ARE working with your users via a
form, right?!)

It's a bit tough offering appropriate suggestions until we have a clearer
picture of what you are working with...

Regards

Jeff Boyce
Microsoft Access MVP
 
Debra Ann

One option, then, would be to add a validation rule in the form's
BeforeUpdate event. It might look something like (untested):

If Len([YourField])<9 Then
Me![YourField] = Right("000000000" & [YourField],9)
ElseIf Len([YourField])>9 Then
Msgbox "Too long!"
Else
'you probably don't need to do anything here
End If

Good luck (remember to test this)

Regards

Jeff Boyce
Microsoft Access MVP
 
Back
Top