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

Multiple Variable Criteria

P: 4
I'm trying to create a form that has several text boxes for input by the user. I want to use the input as criteria for a query that will then display in a listbox on that same form.
I can get it to work, but i need to have the query use the criteria entered, and if some is left out, ignore it instead of seeing it as a Null value.

For example, I have inputs for First Name, Last Name, City, State

User inputs Last Name and City
The query should return all results by Last Name AND City

I can get it to work only on an OR basis.

Help!
Thank you.
Aug 8 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,396
In the criteria of all the fields you need to use Like "*" & Forms!frmName!ctlName & "*". You also need to Nz the fields in case any of them have Nulls.
Aug 8 '07 #2

P: 4
In the criteria of all the fields you need to use Like "*" & Forms!frmName!ctlName & "*". You also need to Nz the fields in case any of them have Nulls.

I'm not familiar with how to use Nz. Where would I put that?
Aug 8 '07 #3

Rabbit
Expert Mod 10K+
P: 12,396
Expand|Select|Wrap|Line Numbers
  1. SELECT Nz(LastName, "")
  2. FROM Table1
  3. WHERE Nz(LastName, "") Like "*" & Forms!Form1!txtLastName & "*";
  4.  
Aug 8 '07 #4

Post your reply

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