Query Question - Can you help me fix a query that returns the wrong results?

[emaduddeen]

Hi Everyone,

Can you help me fix a query that returns the wrong results?

The query does execute but it returns the wrong results. My form has a single text box where the user enters a value. This value may be a customer ID, or it could be a last name or even a phone number. I would like to construct a query so that it would return results if the value in the text box matches either a customer ID, or a last name or a phone number in the Access database.

Here is what I tried so far.

        strSQL = "Select FirstName + ' ' + LastName AS [Full Name], ID, " & _
                        "Format(PhoneCell, ""(###) 000-0000"") As [Cell Phone], " & _
                        "Format(PhoneHome, ""(###) 000-0000"") As [Home Phone], " & _
                        "Format(PhoneWork, ""(###) 000-0000"") As [Work Phone] " & _
                   "From Customers " & _
                  "Where ID Like ? " & _
                    "Or LastName Like ? " & _
                    "Or PhoneCell Like ? " & _
                    "Or PhoneHome Like ? " & _
                    "Or PhoneCell Like ? " & _
                  "Order By 1 "

The query returns extra not needed rows because some of those rows don't have a cell phone or a work phone. I think I need to add something extra to the "Or" keywords in rounded brackets but I'm not sure how to go about this the correct way.

Thanks.

Truly,
Emad

 

[jmcilhinney]

Is your ID column numeric or text?

 

[emaduddeen]

Hi,

It's numeric.

Searching by ID does return results but I also get results from phones even though there is no phone numbers in some of those columns.

Truly,
Emad

 

[jmcilhinney]

When you say that it's numeric, is it actually a number or is it text containing digits, because they are very different. Basically, you can't use LIKE with numbers.

 

[emaduddeen]

Hi,

Later today I will try to change "Like" to "=" because it's an auto incrementing number column.

Maybe that's why I was getting strange results.

Thanks.

Truly,
Emad

 

[emaduddeen]

Hi,

The = did not fix the problem but I was able to create multiple queries and get the results as needed.

Thanks for the help.

Truly,
Emad