Counting the number of characters in a text field

[Mary]

My question is very simple for you experts.

I have a list of phone number in a table and some phone numbers were entered
incorrectly because they are less than 10 digits. Is there an easy way to
QUERY the table to find all phone number that have less than 10 digits.

Any advice and assistance would be most appreciated!

Thank you.

 

[John Spencer MVP]

If all that is stored is ten number characters and not any formatting
characters (spaces, dashes, parens, etc.) then you can search using the
following which will return any field that is not null and does not consist of
exactly 10 number characters

Field: PhoneNumber
Criteria: Not Like "##########"


Or you can use the VBA Len function. This would not pick up phone numbers
that contain 10 characters but the ten were not all numbers. That is you
would not see a record with "()123-4576" returned with the criteria below
while you would with the criteria above.

Field: LengthOfNumber: Len(PhoneNumber)
Criteria: < 10

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ron2006]

Create query with fileds that you want to see.

Add one more field called something like:

PhoneLength:Len([tablename]![PhoneNumberFieldName])

Add criteria for that new field of

< 10

(or any other number you desire. This is dependent on the format of
the field you are calling phone number. If you have it as a text field
with the - and () part of the data, the test may take some tweaking to
get what you want. But this should give you a start.)

Ron

 

[Mary]

Fantastic -- you really got me thinking about other options ... Much
appreciated!
Enjoy the rest of your day.