[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Validate number of digits inputted in a field in an access query

Posted on 2007-07-30
5
Medium Priority
?
587 Views
Last Modified: 2012-08-13
Is there a way to check the number of characters inputted into a field in access by using a query.

For example I have a field that should have a five digit filed inputted.  The field may have leading zeroes.
I am trying to determine those fields which have only four digits inputted which is an error.
 04589  is OK
4589  is not
How can I check for this?
0
Comment
Question by:morinia
  • 2
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 19592395
The quickest way would be to go to your text box's Properties, find the Input Mask property, and type 00000.

Also, whatever ControlSource this is bound to will have to be in text format, as numeric formats do not allow for trailing zeros.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 19592400
{correction}

The quickest way would be to go to your text box's Properties, find the Input Mask property, and type 00000.

Also, whatever ControlSource this is bound to will have to be in text format, as numeric formats do not allow for leading zeros.
0
 
LVL 10

Expert Comment

by:LenaWood
ID: 19592403
You could use the format property of the field in the table and put in 5 zeros:

00000

That will make the field be 5 digits.  If someone entered 4589 then Access would show it as 04589.  This is assuming that your field is a number field.

Does that help you in solving your issue?
Lena
0
 
LVL 85
ID: 19592408
You could use a query to show you all records that meet your criteria:

SELECT * FROM SomeTable WHERE Len(YourField)=5
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 19592415
Or, to show those that don't meet your criteria:

SELECT * FROM SomeTable WHERE Len(YourField)<>5
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

834 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