asked on

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

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?

Jim Horn

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.

Jim Horn

{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.

LenaWood

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

Scott McDaniel (EE MVE )

You could use a query to show you all records that meet your criteria:

SELECT * FROM SomeTable WHERE Len(YourField)=5

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial