phone number format query

[md6635]

I have field with phone numbers in following format

123-1234-1234

however there are some records that are not in that format like
1234567890

also there are some records that are repeat of same numbers
111111111

i want to write query in design mode that will isolate these bad records that are not in correct format.

How do I do that.

 

[Andrzejek]

To get the numbers like this: [tt]123-123-1234[/tt] you would ask for:

....
WHERE PhoneNo LIKE '*-*-*'

So to get [tt]1231231234[/tt] do:

....
WHERE PhoneNo [blue]NOT[/blue] LIKE '*-*-*'

Also, if you want to be more exact, instead of * use 'single character wild card' (whatever that is in Access):

....
WHERE PhoneNo [blue]NOT[/blue] LIKE '???-???-????'

Have fun.

---- Andy

 

[md6635]

great the first solution worked however

how do i determine if same number is repeated all the way thru the phone number like

111-111-1111

 

[Andrzejek]

The easy (read: lazy) solution would be to order them by the telephone number and 'see'

To do it more ‘sophisticated’ way would be to read phone numbers one by one and compare digit by digit.


Have fun.

---- Andy