multiple check query

[erfigge]

In a query I have a record field that I need to specify criteria for. The records in that field are of the form 2xxxxx, 3xxxxx - 9xxxxx ect. (the 2,3,4,5,6,7,8,9 denote different systems). Well, I have built a form with 9 checkboxes for the user to select which forms to run a query on. The problem is that I can't get the syntax correct on joining the different options. I have looked at the massively long tek tip about building user forms, but that really didn't make sense to me.

I even tried setting up 9 different variables in the critera section of the query, but if you leave out just one the query gives you the whole record set. So instead of doing the 9 if's, how can I join them into 1 string to search like this: Like"4*" AND Like"6*" AND Like"7*" depending on what the user selects?

If system2.Value = True Then
search2.Value = "2"
End If
If system3.Value = True Then
search3.Value = "3"
End If
If system4.Value = True Then
search4.Value = "4"
End If
If system5.Value = True Then
search5.Value = "5"
End If
If system6.Value = True Then
search6.Value = "6"
End If
If system7.Value = True Then
search7.Value = "7"
End If
If system8.Value = True Then
search8.Value = "8"
End If
If system9.Value = True Then
search9.Value = "9"
End If

Also, right now I am outputing the results back to the user form and then calling the query. Is there a way to send a variable directly back to the criteria field without it being on the user form? Thanks.

Eric.

 

[lupins46]

Is the datatype of the field to be tested text or number?

 

[erfigge]

The data type is text.

 

[lupins46]

In the query grid you can use multiple iif() statements to set your criteria.

the first two would be:

Like iif(Forms!formname!system2 = true, "2","A") & "*"
Like iif(Forms!formname!system3 = true, "3","A") & "*"

(change formname to your own form name)

Put one of these statements for each starting value on separate rows in the criteria for the column you want to search.

Using "A" as the alternative in each case means that it will look for values beginning with A which should mean no records are selected for that test.

 

[FancyPrairie]

Check out the FAQ faq181-5497 It contains a function that will return the Where clause for you. It works for single and multi-select list boxes, combo boxes, text boxes, date ranges, options groups, and check boxes. It's a good routine to add to your library so you can reuse it in other dbs. You only have to do 3 things to make it work:

1. Create a new module and copy and paste the code from FAQ into your new module.
2. Set your tag properties as specified in the FAQ
3. Open your report as specified in the FAQ

Most people have trouble setting the tag property correctly. In your case the tag property for each of the check boxes would look something like this:

Tag Property for 1st check box:
Where=YourTableName.YourFieldName,String,Like,2*;

Tag Property for 2nd check box:
Where=YourTableName.YourFieldName,String,Like,3*;

Tag Property for 3rd check box:
Where=YourTableName.YourFieldName,String,Like,4*;

and so on...

After you have set the tag properties correctly, place a button on your form and in the OnClick event of the button, enter this code: MsgBox BuildWhere(Me)

Now, select a combination of checkboxes. The msgbox will display the where clause for you.

 

[erfigge]

Hmm, that makes sense. I tried to understand that tip and it just didn't seem to make sense to me.

However, I tried the iif statements and it seems to work. Now I have to figure out why it suddenly gives me this error:

Run Time Error '2001':
You canceled the previous operation.

It gets stuck on this line:

DoCmd.OpenQuery "Final Report", acViewNormal, acEdit

Any thoughts on that?

 

[erfigge]

Thanks for everyone's help. I just replaced the open query with the open report and everything seems to be working fine.