Finding Invalid Phone Numbers Query?

[Dano]

O.K. Here's another one. I have a database that has phone numbers in the (XXX)XXX-XXXX format, but in the table properties it's not set up with an input mask or validation rule (Just limited to 13 characters). I believe the input form was set in the (XXX)XXX-XXXX fomat. Whickh explains why the NULL fields still have the ( ) - in them. SO, I'm looking for a way to find all the phone numbers that aren't in the (XXX)XXX-XXXX format. Like ( )XXX-XXXX or (XXX)XXX-X, ( ) - , etc. Any suggestions?

 

[RichO]

So really what you want to look at is the 1st, 5th and 9th characters to make sure they are ( ) - and also check for the length of the string to be 13. Set up an expression field in your query:

=Left$(MyField,1) & Mid$(MyField, 5, 1) & Mid$(MyField, 9, 1)

In the criteria for that field use <>"()-"

Then you also want to set another expression:

=Len(Trim$(MyField))

and in the criteria (or, not and) use <>13

 

[Dano]

hmmm.

That didn't work? I got a "Data Type Mismatch In Criteria Expression" error. Just to clarify all the records DO have the ( ) - in them, but some are weird like (XXX)XXX-X (like they didnt put the area code). And some are like this ( )XXX-XXXX where the area code was omitted and others just have the area code and no number (XXX) - . All of theses are what I'm trying to find. Thanks for your help!!!!

 

[RichO]

Not sure what the problem is.

I've attached a sample DB (Access 2000) containing a table and a query. This example works for querying the improperly formatted phone numbers. Compare the two and maybe you can figure out where the error is coming from.

 

[Dano]

I'll try...

I'll try updating to 2000. I'm still on Access 97 (I know...I know...procrastinator!) It's installing right now. I'll let you know if it changes anything. Thanks!

 

[Dano]

Almost!

O.K. I updated to 2000 and it seems to be working better, but it still doesn't give me phone numbers without area codes. My data was a little different than you had in your table. So, I changed it to resemble my data. Take a look. I believe the form used to enter the data automatically in my table put the "( ) - " in in the correct spots too. So, Blank phone numbers have a "(" in the 1st spot a ")" in the 5th position and a "-" in the 9th position. So, it makes sense to me that a number like "( )XXX-XXXX" wouldn't show up with the query as it is, because technically it would still be 13 characters right (assuming spaces count as a character)? But, I can't figure how to skew it to show those too?

Thanks in advance for your help...

Dan

 

[RichO]

Are you saying that the query is not finding those with spaces in place of the numbers, with the ()- in the right positions?

When the query is checking the the string length it is using the Trim$ function to trim the spaces off the string so I would think it would catch those as well.

To catch those without area codes simply add criteria to the first field (the entire phone number field) and step down to another "OR" condition:

Like "( )*" 3 spaces in between the parenthesis

Also remove the additional criteria you added to the other field, ie: 13 and Like "( )*"

 

[Dano]

Bingo!

I'm retarded. I forgot to put the criteria in the right field. It's working now. You Rock Man thanks!