Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Allow only numbers with a fixed number of characters

Posted on 2011-03-11
10
Medium Priority
?
748 Views
Last Modified: 2012-06-21
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
Comment
Question by:skull52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 21
ID: 35111491
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35111497
<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
 
LVL 21
ID: 35111504
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 10

Expert Comment

by:conagraman
ID: 35112582
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
 
LVL 10

Expert Comment

by:conagraman
ID: 35112623
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
 

Author Comment

by:skull52
ID: 35112626
Thanks to you all but Cap's solution worked perfectly, thanks Conagraman thanks for the kink
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35112631
one more time
use this one

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




0
 
LVL 10

Expert Comment

by:conagraman
ID: 35112642
woops didnt see your post skull52
glad you were able to make it  worlk : )
0
 
LVL 21
ID: 35112942
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
 

Author Comment

by:skull52
ID: 35113018
Thanks Coach
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question