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

Access Query to Accept multiple check box parameters

P: 1
Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you
Nov 9 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 344
Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you
I would create a function that built the criteria up field by field and call this function in the after_update event of each field. Something like this
Expand|Select|Wrap|Line Numbers
  1. sub SetCriteria()
  2. dim strSQL as string
  3. strSQL=""
  4. if Field1 then strSQL="Field1=true"
  5.  
  6. if Field2 then
  7.   if len(strSQL)>0 strSQL=strSQL & " OR "
  8.   strSQL=strSQL & "Field2=true"
  9.  
  10. if Field3 then
  11.   if len(strSQL)>0 strSQL=strSQL & " OR "
  12.   strSQL=strSQL & "Field3=true"
  13.  
  14. etc.
  15.  
  16. if len(strSQL)>0 then strSQL= " WHERE " & strSQL
  17.  
  18. me.recordsource="Select * from tblSomething " & strSQL & ";"
  19. end sub
  20.  
or use the strSQL as the where critieria in your query
Nov 9 '07 #2

Post your reply

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