Limited number of characters in a table

[thetcrew004]

I have a table that allows 6 characters (text) in the field, and I want all 6 characters need to be filled. For example: If a user puts in less than 6 characters (12345 or 9999), I want zeroes to be automatically added in the front (012345 or 009999). Any suggestions as how I can set this in a table?



Thank you in advance!

[ranman256]

In the textbox Afterupdate event, pad the value....

sub txtBox_afterupdate()
TxtBox=String(6-len(txtBox),"0")
end sub

[JoeM]

You typically would want to do this on the Form level (users should NEVER have direct access to the tables - all manual data entry should be controlled through Forms).
On your Form, you can use the BeforeUpdate event of the Form to check and pad the leading zeroes for that field.

For example, the code might look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim curEntry As String
    curEntry = Me.FieldName
    If Len(curEntry) < 6 Then
        Me.FieldName = Right("000000" & curEntry, 6)
    End If
End Sub

[June7]

Or: Me.textboxName = Format(Me.textboxName, "000000")

Be sure to set the field length in table design.

Might also want to set Validation Rule to make sure user doesn't enter more than 6 digits and no alpha.

[orange]

Is part of the criteria --no alpha, no special characters --ONLY NUMERIC 0-9?

[thetcrew004]

Thanks for the suggestion, but I want to put this in the table. Any thoughts that I can do that.

[thetcrew004]

Is part of the criteria --no alpha, no special characters --ONLY NUMERIC 0-9?

That's correct. Just number only (0-9)

[orange]

What happened when you tried suggestions by others?
What worked? Errors? etc

[JoeM]

Thanks for the suggestion, but I want to put this in the table. Any thoughts that I can do that.

You cannot place VBA on a table.

And users should NEVER be accessing or entering data directly into Tables.
They should be going in through Forms, where you can set all sort of rules and validations like these.

[CJ_London]

in a table you could store as a number and format it using the format property - you would use 000000

[JoeM]

in a table you could store as a number and format it using the format property - you would use 000000

Just be aware that custom formatting a number does not change the value, only the appearance.
So, if you enter a value like 123, it will be displayed as "000123", but its value will still be 123 and its length will be 3, not 6.