multiple search criteria

[apoll0]

Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).

What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).

What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?

 

[apoll0]

Well, nobody replied... :-(
FYI, it wasn't a good idea. I came up with an alternative...

    searchSQL = "SELECT Guests.*, [StreetNumber] & ' ' & [StreetName] AS Address FROM Guests " & _
        "WHERE (Guests.PhoneNumber Like '*" & Me.PhoneCriteria & "*' AND " & _
        "Guests.Name Like '*" & Me.NameCriteria & "*' AND " & _
        "Guests.StreetName Like '*" & Me.StreetCriteria & "*' AND " & _
        "Guests.PostalCode Like '*" & Me.PostalCriteria & "*')" & _
        "ORDER BY Guests.Name;"

This works just fine, only problem is for Names, i gotta enter something like "Smith*John" to catch all the John Smith's with different middle names out there.

 

[The_Doc_Man]

There are many ways to skin this cat.

Search the forum for the topic "Cascading Combo Boxes" for discussions on using multiple search criteria to refine drilldown-type searches.