Use Multi Select to build query for a report
Posted on 2007-04-09
I have a form with several list boxes that are used to create a report. The user can select from one or many of the list boxes to customize the report. I would like to change one of the list boxes to a multi-select list box. If the user selects multiple items from the list box then each value needs to be listed as an OR criteria in the query.
What was working before the change to a multiselect box was:
If Me!lstRptVariety <> 0 Then
sql = sql & " AND PRODUCT.PRODUCT_VARIETY_ID = " & Me!lstRptVariety
I changed the list box to a multi select and then came up with the horribly written and crude code that follows. Obviously I am very inexperienced in writing For Statements.
For intCurrentRow = 0 To lstRptVariety.ListCount - 1
If lstRptVariety.Selected(intCurrentRow) Then
strItems = strItems & lstRptVariety.Column(0, intCurrentRow) & " OR "
If Right(strItems, 4) = " or " Then strItems = Left(strItems, (Len(strItems) - 4))
sql = sql & " AND PRODUCT.PRODUCT_VARIETY_ID = " & strItems
Current problems with the new coding:
A) I had to eliminate the first IF statement that checked to see if there was a value selected in the list box because for some reason it wouldn't detect a value anymore.
B) I would get an extra OR at the end of the values so I had to add the If Right(strItems, 4).... statement to eliminate that issue
C) It returns this in the Where clause: ((PRODUCT.PRODUCT_VARIETY_ID)=179)) OR (((180)<>False)) OR (((194)<>False)) OR (((222)<>False))
but it should return this...
((PRODUCT.PRODUCT_VARIETY_ID)=179 Or (PRODUCT.PRODUCT_VARIETY_ID)=180 Or (PRODUCT.PRODUCT_VARIETY_ID)=194 Or (PRODUCT.PRODUCT_VARIETY_ID)=222))
Can anyone help me with the correct way to utilize a multi select box to build a query for a report?