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

Entering criteria into a query using VBA

P: 31
I have a question on passing data to the criteria of a parameter of a parameter query using VBA. I have a query set up that has ten different fields in it. One is field named “status”, this field contains numbers from 0 to 9 depending on the type of defect a particular part was rejected for. I need to filter the data from the query based on a number or numbers in the status field. On the form that calls this query is a series of 7 check boxes that the user can select from to omit particular data from the record set that’s returned. Multiple check boxes can be selected at the same time. How do I enter the numbers into the criteria field of the status parameter based on the check boxes selected? Do I need to run the query several times for each check box selected, or can I run the query once with several different numbers placed in the criteria field of the status parameter?
Jul 20 '10 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,134
My answer here is based on your question and no thought on my part as to whether what you are doing is the best way to do it or not. I will leave that for you to decide as you are in the best position (as the developer of your database) to make that decision.

Assuming the check boxes are named
Check0 for status=0
Check1 for status=1

The following query is one way to do it
Expand|Select|Wrap|Line Numbers
  1. SELECT The,List,Of,Fields,To,Select
  2. FROM YourTableName
  3. WHERE Status=IIf([Forms]![YourFormName]![Check0].[value],0,-1) 
  4.    Or Status=IIf([Forms]![YourFormName]![Check1].[value],1,-1) 
  5.    Or Status=IIf([Forms]![YourFormName]![Check2].[value],2,-1)
  6.    Or Status=IIf([Forms]![YourFormName]![Check3].[value],3,-1)
  7.    Or Status=IIf([Forms]![YourFormName]![Check4].[value],4,-1)
  8. etc etc
Presumably there is no status=-1 so in each IIF the -1 is used for the equality comparison if the checkbox is not ticked and the other number is used if it is
Jul 20 '10 #2

P: 31
Thank you I will give your suggestion a try.
Jul 21 '10 #3

Post your reply

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