Parenthesis and periods in criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
zombeese said:
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.
Click to expand...

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.
 
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),"-")
 
Thank You Marshall. It worked perfectly!

Marshall Barton said:
zombeese said:
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.
Click to expand...

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.
Click to expand...
 
There is no appropriate format as we have international phone number which
are not universal in format. At least not yet.

KARL DEWEY said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top