"tiered" query (for lack of better word)

[DJM1216]

I would like to create a query that will work in steps, if this is at all possible. I'm using some internal company numbers, however to illustrate what I'm looking for I'll use a telephone number.

Assuming that the telephone number field is XXX-XXX-XXXX (10 digits).

I would like a query that would first look for an exact match (XXX-XXX-XXXX). If that is found, return the value queried on.

If that is not found, next try to match on the first 6 digits (XXX-XXX)

If that is not found, finally match on only the first 3 digits (XXX)

Is there a query that would work like this?

 

[Norie]

How are you getting the values to look for?

Could you not just use the Like operator in the criteria?

 

[DJM1216]

Sorry, I should have been more clear. For the sake of argument, let's assume that the first 3 digits of a phone number represent a state, second 3 represent a county and the last 4 represent a city.

I want to create a form where someone would enter a 10 digit phone number and the query will look on a master table of phone numbers for a match. If the query can match on all 10 digits, the city would return, if no match, try to match on the first 6 digits to return the county, and so on.

Does that help>?

 

[tails]

are u using a programming program at all, eg visual basic, c, c++?
or are u just using the database to create the program,
because u can use strings to define what it is searching for
eg
[phone number] dim as string 10 - that is the sort of code, im not completely sure what it is, but it makes it so you can only enter 10 characters
then when doing a search you just make it do a search on the 10 numbers, then if that dont work eg
if search10 = false then
search6 = adodb.recordset.search leftbound 6
which will search for the first 6 characters of the database
then
if search6 = false then
search 3 = adodb.recordset.search leftbound 3

Just so you know this isn't the exact code. but i have the code at home so i can have a look tonight when i get home from the slip knot concert....
if i cant find it tonight then possible tomorrow night, but sometime soon

 

[DJM1216]

I was hoping to create this query in the database, however I can see that it may be complex so I'm not sure what needs to be done.

Thanks for all of your help.