Like & wildcard

K

kurtn

I have got a query that could have up to 4 parameters. The user can enter
none or any combination. The problem is that if any of the fields in the
table are blank then it won't match. If they enter a value for parameter "A"
but leave the rest of the paramters blank, even if there is a matching record
for what they want in "A" if any of the other fields in the db are blank then
it won't return the record.

Here is my statement:

select fieldA, fieldB, fieldC, fieldD
from tableA
where fieldA LIKE "*" & [Enter A] & "*" AND
fieldB LIKE "*" & [Enter B] & "*" AND
fieldC LIKE "*" & [Enter C] & "*" AND
fieldD LIKE "*" & [Enter D] & "*"

what am I doing wrong?
 
V

Van T. Dinh

Try:

SELECT fieldA, fieldB, fieldC, fieldD
FROM tableA
WHERE
((fieldA LIKE "*" & [Enter A] & "*") OR (fieldA is Null)) AND
((fieldB LIKE "*" & [Enter B] & "*") OR (fieldB is Null)) AND
.....
 
K

kurtn

Whoo! that worked.

thanks

Van T. Dinh said:
Try:

SELECT fieldA, fieldB, fieldC, fieldD
FROM tableA
WHERE
((fieldA LIKE "*" & [Enter A] & "*") OR (fieldA is Null)) AND
((fieldB LIKE "*" & [Enter B] & "*") OR (fieldB is Null)) AND
.....

--
HTH
Van T. Dinh
MVP (Access)


kurtn said:
I have got a query that could have up to 4 parameters. The user can enter
none or any combination. The problem is that if any of the fields in the
table are blank then it won't match. If they enter a value for parameter "A"
but leave the rest of the paramters blank, even if there is a matching record
for what they want in "A" if any of the other fields in the db are blank then
it won't return the record.

Here is my statement:

select fieldA, fieldB, fieldC, fieldD
from tableA
where fieldA LIKE "*" & [Enter A] & "*" AND
fieldB LIKE "*" & [Enter B] & "*" AND
fieldC LIKE "*" & [Enter C] & "*" AND
fieldD LIKE "*" & [Enter D] & "*"

what am I doing wrong?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top