Query not returning empty when no parameters are given.

[ljstern]

I have a query that has 4 "AND" parameters. The problem I am having is that when I leave all the parameters blank, all records are shown. I need for it to come up empty, so that no records are shown unless parameters are actually given. Here is the SQL for the query:

SELECT qryPerson.PERSON, qryPerson.Lname, qryPerson.Fname, Contacts.Group_name, Contacts.Subgroup_name 
FROM (qryPerson LEFT JOIN Contacts ON qryPerson.PERSON = Contacts.PERSON) LEFT JOIN Associates ON Contacts.PERSON = Associates.PERSON 
WHERE (((qryPerson.Lname) Like [Forms]![Search Form]![optLname_txt] & "*") AND ((qryPerson.Fname) Like [Forms]![Search Form]![optFname_txt] & "*") AND ((Contacts.Group_name) Like "*" & [Forms]![Search Form]![optAffil_txt] & "*") AND ((Contacts.Subgroup_name) Like "*" & [Forms]![Search Form]![optAffil2_txt] & "*")) 
ORDER BY qryPerson.Lname, qryPerson.Fname;
 

I really need the query to return an empty set if no parameters are met. Any help would be appreciated.

[FishVal]

Hi, there.

Try criteria like the following:

..... Like IIf(IsNull(<FormField reference>),"", <FormField reference>) ...
 

Regards,
Fish.

[ljstern]

Thank you. I'll try that.

[ljstern]

It worked! Thank you!