Search Menu - How to use multiselection with check boxes

Hello,

I am quite begginer in MS Access and I have a problem with forming search menu. I used help from http://allenbrowne.com/ser-62.html but I have still some problems.
I have check boxes for Products: GT, ST, Nuclear, Generator. Then I have in the Detail part of the SearchNews form the answers that will be showned after selection of Products. I want to have possibility to select from 1 to 4 product at once. But when I used the code from the site above, I can only use one, cause if i tag GT and ST it takes only records with those 2 product at the same time.

I want to have the records with only GT OR only ST OR with both. I saw some codes for that, but that was a lot of combination for that, and I have also other check boxes with 22 possibility to choose... so in that case this way is too long. Is there short code, macro or other way to use search possibilities for multiple check box selection?

I hope that I decribed the code clearly. I would be greatful for any clues.

Greets

Greg

re: Search Menu - How to use multiselection with check boxes

Hi Greg,

Looking at the link, the code there specifically states it will only return records where all criteria match.
What you need to do is replace the AND with OR and that should produce the result you want

re: Search Menu - How to use multiselection with check boxes

Ok, thx for answer.
Actually I found out that its working with AND but only if in that case:

I have product: GT, ST, nuclear.
if i choose GT - give me GT (OK). If i choose GT, ST give me GT ST (OK)
but then if i change again to GT, i have only GT but in the same time ST are excluded (Not OK, i want to see also records that are ST and GT).
So in that case i dont see the records of both ST and GT. how to change that...
I dont want to have ST false if i check it out. i hope you know what i mean...
where should i change that option ...

re: Search Menu - How to use multiselection with check boxes

Ok, I need to see what you are running. Could you please post the actual code you are using to make the selections?

re: Search Menu - How to use multiselection with check boxes

Hey
I didnt changed the code a lot and i left AND but i removed "False" part of every code...and now its working ok. although its not perfect, but at least working :) the only problem is if i choose GT and ST it takes me common value, but i revised it and it could be like that. Thanks for help.

Btw this is the code I used for check boxes:

Private Sub FilterProduct_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************************************************* **********************
'Look at each search box, and build up the criteria string from the non-blank ones.
'************************************************* **********************


'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
If Me.GT = -1 Then
strWhere = strWhere & "([GT] = True) AND "
End If
If Me.ST = -1 Then
strWhere = strWhere & "([ST] = True) AND "

End If
If Me.Nuclear = -1 Then
strWhere = strWhere & "([Nuclear] = True) AND "
End If

If Me.Generator = -1 Then
strWhere = strWhere & "([Generator] = True) AND "
End If

If Me.Coal = -1 Then
strWhere = strWhere & "([Coal] = True) AND "

End If
If Me.Wind = -1 Then
strWhere = strWhere & "([Wind] = True) AND "
End If
If Me.Solar = -1 Then
strWhere = strWhere & "([Solar] = True) AND "
End If

If Me.Geothermal = -1 Then
strWhere = strWhere & "([Geothermal] = True) AND "

End If
If Me.Other = -1 Then
strWhere = strWhere & "([Other] = True) AND "
End If

If Me.GasOil = -1 Then
strWhere = strWhere & "([GasOil] = True) AND "

End If


'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

re: Search Menu - How to use multiselection with check boxes

From what I can see, changing the AND to OR will give you the result you want. i.e.
Change

If Me.GT = -1 Then
strWhere = strWhere & "([GT] = True) AND "
End If

To

If Me.GT = -1 Then
strWhere = strWhere & "([GT] = True) OR "
End If

and all the rest.

At the end, where it set lngLen to strWhere-5, this needs changing to -4

Hopefully this will work