Allow only numbers with a fixed number of characters
I have an Access 2007 table with 5 columns that I only want to allow numbers with a fixed character length of 3, therefore I have set the data type to number and the field size to byte with a mask of 000 which works except it drops the leading zero, I know I can change the data type to Text but that will allow numbers and letters I only want numbers. I have tried setting the field size to integer with the same results. Any incite on this would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops ... try again
Numbers data types do not store leading zeros,. Leading zeros is really about formatting and not part of the numeric value.
Numbers data types do not store leading zeros,. Leading zeros is really about formatting and not part of the numeric value.
i think this should work for you
in the properties of the textbox/field your user would enter the data.
on the data tab in the property "validation rule" put this
Is Null Or Like "????" And Not Like "*[!a-z]*"
(see the pic)
here is a good list of validation rules i found
http://www.everythingaccess.com/tutorials.asp?ID=Validation-Rules
val.bmp
in the properties of the textbox/field your user would enter the data.
on the data tab in the property "validation rule" put this
Is Null Or Like "????" And Not Like "*[!a-z]*"
(see the pic)
here is a good list of validation rules i found
http://www.everythingaccess.com/tutorials.asp?ID=Validation-Rules
val.bmp
my bad use this line
Is Null Or Like "????" And Not Like "*[a-z]*"
the difference is the exclamation point has been removed next to the a in [a-z]
the ! means not
Is Null Or Like "????" And Not Like "*[a-z]*"
the difference is the exclamation point has been removed next to the a in [a-z]
the ! means not
ASKER
Thanks to you all but Cap's solution worked perfectly, thanks Conagraman thanks for the kink
one more time
use this one
Is Null Or Like "???" And Not Like "*[a-z]*"
use this one
Is Null Or Like "???" And Not Like "*[a-z]*"
woops didnt see your post skull52
glad you were able to make it worlk : )
glad you were able to make it worlk : )
As long as you do not need to ever do any math with the value then converting to text will work without any extra effort later or additional overhead..
Note:
I prefer to use numeric data types whenever possible. You really do not have to change the data type to text to do what you want.
Note:
I prefer to use numeric data types whenever possible. You really do not have to change the data type to text to do what you want.
ASKER
Thanks Coach
Use display formatting options to add the leading zeros back. where they are need on form and reports.