Parenthesis and periods in criteria

[zombeese]

My last question along these lines was not worded properly.

I am inputing some phone numbers from many different spreadsheets. Different
people do the original data entry in a variety of diffent
formats

For Example
718) 555-1212
718.555.1212
718 555 1212
718-555-1212

I want to build a query which identifies periods and parenthesis.

I tried a query wherein I used *)* and "*)*" in the criteria. Both of these
didn't work. What should I use?

Please note that sometimes the periods or parenthesis are appropriate, so I
can not use a query which automaticly updates or changes the data.

 

[Klatuu]

Try
WHERE IN( ".", "(", ")")

 

[Marshall Barton]

My last question along these lines was not worded properly.

I am inputing some phone numbers from many different spreadsheets. Different
people do the original data entry in a variety of diffent
formats

For Example
718) 555-1212
718.555.1212
718 555 1212
718-555-1212

I want to build a query which identifies periods and parenthesis.

I tried a query wherein I used *)* and "*)*" in the criteria. Both of these
didn't work. What should I use?

Please note that sometimes the periods or parenthesis are appropriate, so I
can not use a query which automaticly updates or changes the data.

If you want to find all records that contain ) or . then you
can use the criteria:
LIKE "*[).]*"

If you want to get rid of those characters, then you could
try using the Replace function to change those characters to
a space. E.g.
Replace(Replace(Replace(phone,")"," "), "."," "). " "," ")

The outermost Replace is changing two spaces to one space.

 

[KARL DEWEY]

You did not state what the appropriate format should be. If they are to be
all the same then why not remove all and use a format for display like --
Format([YourField],"000-000-0000")
It takes 6615554444 and displays 661-555-4444

You can find the punctuation like this --
InStr([YourField],")")
It tells the location of the punctuation. If you use criteria >0 then only
those records with the punctuation are listed.

You can also start looking for the punctuation at a specified point in the
string like this --
InStr(Mid([YourField],5),"-")

 

[zombeese]

Thank You Marshall. It worked perfectly!

My last question along these lines was not worded properly.

I am inputing some phone numbers from many different spreadsheets. Different
people do the original data entry in a variety of diffent
formats

For Example
718) 555-1212
718.555.1212
718 555 1212
718-555-1212

I want to build a query which identifies periods and parenthesis.

I tried a query wherein I used *)* and "*)*" in the criteria. Both of these
didn't work. What should I use?

Please note that sometimes the periods or parenthesis are appropriate, so I
can not use a query which automaticly updates or changes the data.

If you want to find all records that contain ) or . then you
can use the criteria:
LIKE "*[).]*"

If you want to get rid of those characters, then you could
try using the Replace function to change those characters to
a space. E.g.
Replace(Replace(Replace(phone,")"," "), "."," "). " "," ")

The outermost Replace is changing two spaces to one space.

 

[zombeese]

There is no appropriate format as we have international phone number which
are not universal in format. At least not yet.

You did not state what the appropriate format should be. If they are to be
all the same then why not remove all and use a format for display like --
Format([YourField],"000-000-0000")
It takes 6615554444 and displays 661-555-4444

You can find the punctuation like this --
InStr([YourField],")")
It tells the location of the punctuation. If you use criteria >0 then only
those records with the punctuation are listed.

You can also start looking for the punctuation at a specified point in the
string like this --
InStr(Mid([YourField],5),"-")

My last question along these lines was not worded properly.

I am inputing some phone numbers from many different spreadsheets. Different
people do the original data entry in a variety of diffent
formats

For Example
718) 555-1212
718.555.1212
718 555 1212
718-555-1212

I want to build a query which identifies periods and parenthesis.

I tried a query wherein I used *)* and "*)*" in the criteria. Both of these
didn't work. What should I use?

Please note that sometimes the periods or parenthesis are appropriate, so I
can not use a query which automaticly updates or changes the data.