Excluding Phone Numbers using a query

[Scott O'Neal]

OKay, here's the lowdown, I have written a simple query to list the phone numbers I have entered into a client database. I have 3 fields in my table, (autonumber, date, and phone number). I wrote the first query and it produced a list of phone numbers dating back to the inception of the database. I then added a critria statement to limit the list to the last 31 days, >Now()-31. Now I need to get rid of any local phone calls that were placed, showing only the phone numbers outside of the 615 area code. I have tried quite a bit, but nothing is working. Any tips or suggestions?

 

[MSAccessRookie]

OKay, here's the lowdown, I have written a simple query to list the phone numbers I have entered into a client database. I have 3 fields in my table, (autonumber, date, and phone number). I wrote the first query and it produced a list of phone numbers dating back to the inception of the database. I then added a critria statement to limit the list to the last 31 days, >Now()-31. Now I need to get rid of any local phone calls that were placed, showing only the phone numbers outside of the 615 area code. I have tried quite a bit, but nothing is working. Any tips or suggestions?

You have not provided enough details for a definitive answer, since the answer will depend primarily on the contents of the PhoneNumber Field.

1. If the Field has a structured Format such as (aaa)xxx-xxxx, then you could use the Instr() Command to seek out the Area Code and exclude the local calls.
2. If the Field has an unstructured Format such that both xxx-xxxx and (aaa)xxx-xxxx are valid for a local number, then you would first need to locate any numbers without area codes and then use the Instr() Command to screen out the Area Code and exclude any remaining local calls.

There may be other considerations, but these two could be a good start.

-- Rookie

 

[Scott O'Neal]

OK, it looks as if the format is structured. when I look at the properties for that field, is is showing an input mask. At least I hope that means structured.

Now I need to look up instr commands and figure out how to write on.
Thanks!

 

[MSAccessRookie]

OK, it looks as if the format is structured. when I look at the properties for that field, is is showing an input mask. At least I hope that means structured.

Now I need to look up instr commands and figure out how to write on.
Thanks!

I did not want to presume what you knew or did not know. Information about Instr() can be found below.

http://office.microsoft.com/en-us/access-help/instr-function-HA001228857.aspx