multiple select list box for query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a form with three criteria list boxes, Country, Material and
Intended that I'm using as criteria in a query. While this is working, I'd
like to be able to make multiple selections in the list boxes and have my
query use these multiple values. I know I need to code this in VBA, but not
exactly sure what it should look like. Also, what to I attach the code to? Is
it attached to the form, the query, the command button that opens the query,
etc?
 
I've got a form with three criteria list boxes, Country, Material and
Intended that I'm using as criteria in a query. While this is working,
I'd like to be able to make multiple selections in the list boxes and
have my query use these multiple values.
Click to expand...

just build the string slowly and carefully: you can iterate each listbox
and string together the ORs and ANDs

for i = 0 to lisBox.listcount-1
if lisBox.Selected(i)
if len(strWhere)>0 then strWhere = strWhere & " OR "
strWhere = strWhere & "Box = """ & lisBox.ListItem(i) & """"

end if
next i

or you could probably do the same thing with IN operators, and so on. I
guess you want to end up with a command that looks like

WHERE Box="Cardboard" OR Box="Plastic"
AND Weight="Heavy" OR Weight="Very Heavy"
AND Danger="Minimal" OR Danger="None"

Hope that helps


Tim F
 
Back
Top