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?
-
-
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
etc
The following query is one way to do it
[code=sql]
SELECT The,List,Of,Fie lds,To,Select
FROM YourTableName
WHERE Status=IIf([Forms]![YourFormName]![Check0].[value],0,-1)
Or Status=IIf([Forms]![YourFormName]![Check1].[value],1,-1)
Or Status=IIf([Forms]![YourFormName]![Check2].[value],2,-1)
Or Status=IIf([Forms]![YourFormName]![Check3].[value],3,-1)
Or Status=IIf([Forms]![YourFormName]![Check4].[value],4,-1)
etc etc
[/code]
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 -
Thank you
Thank you I will give your suggestion a try.Comment
Comment