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

Query with multiple cirteria

P: 49
I am trying to write a query that gets its criteria from choices in 8 fields on a form. I want to be able to run the query if you make a choice in all of the fields or only one.

Any ideas?
Thanks
Jun 4 '07 #1
Share this Question
Share on Google+
6 Replies


JConsulting
Expert 100+
P: 603
I am trying to write a query that gets its criteria from choices in 8 fields on a form. I want to be able to run the query if you make a choice in all of the fields or only one.

Any ideas?
Thanks
Always LOTS of ideas.

What field types are they?
Jun 4 '07 #2

P: 49
Always LOTS of ideas.

What field types are they?
Text Fields ands a couple of look ups
Jun 11 '07 #3

JConsulting
Expert 100+
P: 603
Text Fields ands a couple of look ups

in the coresponding fields in your query, in the criteria you can use the following
Expand|Select|Wrap|Line Numbers
  1. iif(nz([forms]![yourformname]![yourtextbox],"") ="",[yourfieldname],[forms]![yourformname]![yourtextbox])
  2.  
change the formname, textbox name and the field name to yours.

Use this same structure for each field you're using.

what it does is looks at the form (it must be open) in each of those text or combo boxes to see if something has been entered or selected...and if it has, it uses it as criteria....if there's nothing in it...it returns everything for that field.

Let me know if you run into any snags.
J
Jun 11 '07 #4

P: 49
in the coresponding fields in your query, in the criteria you can use the following
Expand|Select|Wrap|Line Numbers
  1. iif(nz([forms]![yourformname]![yourtextbox],"") ="",[yourfieldname],[forms]![yourformname]![yourtextbox])
  2.  
change the formname, textbox name and the field name to yours.

Use this same structure for each field you're using.

what it does is looks at the form (it must be open) in each of those text or combo boxes to see if something has been entered or selected...and if it has, it uses it as criteria....if there's nothing in it...it returns everything for that field.

Let me know if you run into any snags.
J
Thanks Work GREAT!
T
Jun 19 '07 #5

P: 49
This code worked great in about 8 other places now i am tryng to use in an existing database and when i run the query it gies me a syntex error and then it makes itself into a Union query andy Idea why?
See SQL below
SELECT [EMP_LIST].[Associate Number], [EMP_LIST].[Assc Lname], [EMP_LIST].[Assc FName], [EMP_LIST].[Pay Rate], [EMP_LIST].Status, [EMP_LIST].Service, [EMP_LIST].Active, [EMP_LIST].Notes, [EMP_LIST].UNION , [EMP_LIST].dob, [EMP_LIST].doh, [EMP_LIST].aDDR, [EMP_LIST].aDDR2, [EMP_LIST].[tERM dATE], [EMP_LIST].[FT Date], [EMP_LIST].[Hire Date], [EMP_LIST].schhours FROM [EMP_LIST] WHERE ((([EMP_LIST].Status)=IIf(nz(Forms![Emp Info-Edit]!statusfltr,"")="",[EMP_LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP_LIST].Active)=IIf(nz(Forms![Emp Info-Edit]!actfltr,"")="",[EMP_LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP_LIST].UNION)=IIf(nz(Forms![Emp Info-Edit]!unionfltr,"")="",[EMP_LIST]!union,Forms![Emp Info-Edit]!unionfltr)));

Thanks
T
Aug 7 '07 #6

JConsulting
Expert 100+
P: 603
This code worked great in about 8 other places now i am tryng to use in an existing database and when i run the query it gies me a syntex error and then it makes itself into a Union query andy Idea why?
See SQL below
SELECT [EMP_LIST].[Associate Number], [EMP_LIST].[Assc Lname], [EMP_LIST].[Assc FName], [EMP_LIST].[Pay Rate], [EMP_LIST].Status, [EMP_LIST].Service, [EMP_LIST].Active, [EMP_LIST].Notes, [EMP_LIST].UNION , [EMP_LIST].dob, [EMP_LIST].doh, [EMP_LIST].aDDR, [EMP_LIST].aDDR2, [EMP_LIST].[tERM dATE], [EMP_LIST].[FT Date], [EMP_LIST].[Hire Date], [EMP_LIST].schhours FROM [EMP_LIST] WHERE ((([EMP_LIST].Status)=IIf(nz(Forms![Emp Info-Edit]!statusfltr,"")="",[EMP_LIST]!Status,Forms![Emp Info-Edit]!statusfltr)) And (([EMP_LIST].Active)=IIf(nz(Forms![Emp Info-Edit]!actfltr,"")="",[EMP_LIST]!Active,Forms![Emp Info-Edit]!actfltr)) And (([EMP_LIST].UNION)=IIf(nz(Forms![Emp Info-Edit]!unionfltr,"")="",[EMP_LIST]!union,Forms![Emp Info-Edit]!unionfltr)));

Thanks
T

See your other post for the resolution.
J
Aug 10 '07 #7

Post your reply

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