Like & wildcard

[Guest]

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?

 

[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
.....

 

[Guest]

Whoo! that worked.

thanks

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)

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?