By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

Query not returning empty when no parameters are given.

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT qryPerson.PERSON, qryPerson.Lname, qryPerson.Fname, Contacts.Group_name, Contacts.Subgroup_name 
  2. FROM (qryPerson LEFT JOIN Contacts ON qryPerson.PERSON = Contacts.PERSON) LEFT JOIN Associates ON Contacts.PERSON = Associates.PERSON 
  3. 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] & "*")) 
  4. ORDER BY qryPerson.Lname, qryPerson.Fname;
  5.  
I really need the query to return an empty set if no parameters are met. Any help would be appreciated.
Jan 30 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Try criteria like the following:
Expand|Select|Wrap|Line Numbers
  1. ..... Like IIf(IsNull(<FormField reference>),"", <FormField reference>) ...
  2.  
Regards,
Fish.
Jan 30 '08 #2

P: 5
Thank you. I'll try that.
Jan 30 '08 #3

P: 5
It worked! Thank you!
Jan 30 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.