Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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.      
0
skull52
Asked:
skull52
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Numbers data types do not store lead9ng zeros,. Leasing zeros is really about  formatting

Use display formatting options to add the leading zeros back. where they are need on form and reports.
0
 
Rey Obrero (Capricorn1)Commented:
<I know I can change the data type to Text but that will allow numbers and letters I only want numbers.>

try using Text data type
set the Field size to 3
set validation rule   not like "*[!0-9]*"       '<< will only take values 0-9
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Oops ... try again

Numbers data types do not store leading zeros,. Leading zeros is really about  formatting and not part of the numeric value.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
conagramanCommented:
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
0
 
conagramanCommented:
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
0
 
skull52Author Commented:
Thanks to you all but Cap's solution worked perfectly, thanks Conagraman thanks for the kink
0
 
conagramanCommented:
one more time
use this one

Is Null Or Like "???" And Not Like "*[a-z]*"




0
 
conagramanCommented:
woops didnt see your post skull52
glad you were able to make it  worlk : )
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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.
0
 
skull52Author Commented:
Thanks Coach
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now