Use Multi Select to build query for a report

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
End if

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 "
        End If        
Next intCurrentRow
         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?
johnnyloffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jefftwilleyCommented:
yep, instead of creating an OR statement, create an IN(  statement. if the values are numeric, use this

'for numeric fields
    Dim varItem As Variant
    Dim myString As String
    With Me.MyListBox
        For I = 0 To .ItemsSelected.count - 1
            myString = .Column(0, I) & ", " & myString
        Next I
    End With
    myString = Left(myString, Len(myString) - 2)

use it like so

strSQL = "Select * from mytable where myfield in(" & myString & ");"
0
jefftwilleyCommented:
strSQL = "Select * from Product where PRODUCT_VARIETY_ID in(" & myString & ");"

good luck! It's Z-time here.
J
0
Barry CunneyCommented:
Hi Johnny,
Similar to what jeff is advising - I wrote a few generic functions before for an application to do similar stuff

Here are the functions - you may be able to use all or some of the code:

The below function checks if any items in a multi-select list box are selected and returns bound
column values for selected items in an array - also it can be used to return data in array from other columns

Function CheckIfAnyListBoxItemSelected(ctlList As Control, ByRef lCount As Long, ByRef vBoundData() As Variant, _
                                    iNumColumns As Integer, ByRef vColumnData() As Variant) As Boolean
'---------------------------------------------------------------------------------------------------------
' This function checks if any items in a multi-select list box are selected
' 1. List Control object
' 2. ByRef - Count of number of selected items
' 3. Index of List Box column from which to return data
' 4. ByRef - Array containing the bound data
' 5. Array containing column data
'---------------------------------------------------------------------------------------------------------
On Error GoTo Err_Function

Dim varItm As Variant
Dim lIndex As Long
Dim iColumnLoop%

   lCount = 0
   lIndex = 0
   CheckIfAnyListBoxItemSelected = False
   
   For Each varItm In ctlList.ItemsSelected
       CheckIfAnyListBoxItemSelected = True
       lCount = lCount + 1
   Next varItm
   
   If lCount = 0 Then Exit Function
   
   ' Dynamically redimension arrays as necessary
   ReDim vBoundData(lCount - 1)
   ReDim vColumnData(lCount - 1, iNumColumns - 1)
   
     
   ' Loop through each selected item storing in array
   For Each varItm In ctlList.ItemsSelected
     
       vBoundData(lIndex) = ctlList.ItemData(varItm)
        ' For each selected item loop through the number of columns specified
        ' and store the column data
        For iColumnLoop% = 0 To (iNumColumns - 1)
           vColumnData(lIndex, iColumnLoop%) = ctlList.Column(iColumnLoop%, varItm)
        Next iColumnLoop%
       
    lIndex = lIndex + 1
   
   Next varItm

   CheckIfAnyListBoxItemSelected = (lCount > 0)

Exit_Function:
    Exit Function
   
Err_Function:
    CheckIfAnyListBoxItemSelected = False
    MsgBox "Function: CheckIfAnyListBoxItemSelected - " & Err.Number & " " & Err.Description, , "Client Database"
    Resume Exit_Function

'---------------------------------------------------------------------------------------------------------
End Function


The below function builds an SQL IN clause from values in the specified array
Function BuildSQL_In_ClauseFromArrayVals(vValuesArray() As Variant, Optional bStrings As Boolean = False) As String
'---------------------------------------------------------------------------------------------------------
' This function builds and SQL IN clause from an array of Values e.g. In("CT", "RE", "OP")
' 1. Array containing values for In clause
'---------------------------------------------------------------------------------------------------------
On Error GoTo Err_Function

Dim lSize As Long
Dim iLoop As Integer
Dim iIndexNextItem%

    BuildSQL_In_ClauseFromArrayVals = "In("
       
    lSize = -1
    For iLoop = 0 To UBound(vValuesArray)
     If Not IsEmpty(vValuesArray(iLoop)) Then lSize = lSize + 1
    Next iLoop
   
    ' Loop through all the values in the array
    For iLoop = 0 To (lSize)
        ' Put a comma after each item execpt the last item - put closing parentheses after last item
       
        If iLoop + 1 < lSize Then
            iIndexNextItem% = iLoop + 1
        Else
             iIndexNextItem% = lSize
        End If
       
        If iLoop < (lSize) And Not IsEmpty(vValuesArray(iIndexNextItem%)) Then
            If (bStrings) Then
               BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & "'" & CStr(vValuesArray(iLoop)) & "',"
            Else
               BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & CStr(vValuesArray(iLoop)) & ","
            End If
        Else
          If Not IsEmpty(vValuesArray(iLoop)) Then
            If (bStrings) Then
                BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & "'" & CStr(vValuesArray(iLoop)) & "')"
            Else
                BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & CStr(vValuesArray(iLoop)) & ")"
            End If
          End If
        End If
    Next iLoop
 

Exit_Function:
    Exit Function
   
Err_Function:
    MsgBox "Function: BuildSQL_In_ClauseFromArrayVals - " & Err.Number & " " & Err.Description, , "Client Database"
    Resume Exit_Function
 
'---------------------------------------------------------------------------------------------------------
End Function

So a sample use of the above function would be:

If CheckIfAnyListBoxItemSelected(lstRptVariety, lCount&, vSelectedProductVarieties(), 1, vVarietyName()) Then
     
sCriteria$ = "WHERE PRODUCT_VARIETY_ID " &        BuildSQL_In_ClauseFromArrayVals(vSelectedProductVarieties(), True)

sSQL$ = "SELECT * FROM PRODUCT " & sCriteria$

End If

Cheers


B Cunney
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnnyloffAuthor Commented:
Jefftwilley - Thank you for the response.  I used your suggestion but I am getting some strange results.  

It's giving me a run time error '3048'.  Reserved error.

I have inserted a msgbox to see what it is retrieving and it always returns one less result than what I select.  If I select three items it only returns two.

It also is returning the same PRODUCT_VARIETY_ID's no matter which items I select.  So if I select four items, it returns 167, 166, 165,

Any ideas on what I need to change?  This code comes after the SELECT and FROM parts of the sql and is the beginning of a series of clauses within the WHERE.  I have modified the code to the following:

    Dim varItem As Variant
    Dim myString As String
    With Me.lstRptVariety
        For i = 0 To .ItemsSelected.Count - 1
            myString = .Column(0, i) & ", " & myString
        Next i
    End With
    myString = Left(myString, Len(myString) - 2)
    MsgBox myString


If Me!lstRptVariety <> 0 Then
sql = sql & " AND PRODUCT_VARIETY_ID & in(" & myString & ")"
End If
0
johnnyloffAuthor Commented:
BCUNNEY - Sorry, I've been working on the previous code and did not think I'd have more already.  I've got to get some sleep and I'll try your function tomorrow morning.
0
johnnyloffAuthor Commented:
BCUNNEY - Well, I couldn't resist trying the code.  I tried executing the function but it's giving me an error on vSelectedProductVarieties(). It's a compile error saying sub or function not defined.  I'm sure it will do the same thing to me on vVarietyName()) but I looked at your code and I do not know what I should modify.

This is how I've used your code.

If CheckIfAnyListBoxItemSelected(lstRptVariety, lCount&, vSelectedProductVarieties(), 1, vVarietyName()) Then

sCriteria$ = "WHERE PRODUCT_VARIETY_ID " & BuildSQL_In_ClauseFromArrayVals(vSelectedProductVarieties(), True)

sql = sql & " AND PRODUCT_VARIETY_ID = " & sCriteria$

End If
0
Barry CunneyCommented:
Yo need to declare an array vSelectedProductVarieties() and also lstRptVariety should be declared as Listbox control
0
Barry CunneyCommented:
Dim vSelectedProductVarieties() As Variant
Dim vVarietyName() As Variant
Dim lCount&
Dim lstRptVariety As ListBox

Set lstRptVariety = Form.lstRptVariety

Use the above declares before calling the functions
0
johnnyloffAuthor Commented:
BCUNNEY - Thank you.  I can see that I'm closer.  In the msgbox it appears like it is returning the right information.  For example, the sCriteria$ is returning

sCriteria$ = "WHERE PRODUCT_VARIETY_ID In('124','178','205')"

However, I believe I must be using the sCriteria$ incorrectly after that.  I've tried different variations to incorporate it but with no luck.  
0
Barry CunneyCommented:
Hi jOHHNY,
The following is a trick to test your SQL statement:

1. Debug your code and step passed the lines that build up your SQL statement.
2. Then print the variable which contains your full SQL statement to the Immediate Window
e.g. ? SQL$
This will give you the exact SQL that you r code is executing
3. Copy <Ctrl + C>  this SQL statement from the immediate window
3. Then go to Queries, create a new query, goto SQL View, paste in SQL statement and try to run
This will help you identify problems with the SQL statement syntax and construction
0
jefftwilleyCommented:
Johnny,
sorry...I HAD to sleep...

IF you have Headings in your listbox this line changes to

For I = 1 To .ItemsSelected.count

This next line also assumes you're pulling from the first column from the listbox

myString = .Column(0, i) & ", " & myString   ' Where Column(0  is the first column

Looks like BCunney has you rolling though.

Luck!
J
0
Rey Obrero (Capricorn1)Commented:
you can use this short code to build your where criteria

Dim j , sCriteria

with me.lstRptVariety



end with
0
Rey Obrero (Capricorn1)Commented:
sorry about that

you can use this short code to build your where criteria

Dim j , sCriteria

with me.lstRptVariety
      if .itemsselected.count>0 then
          for each j in .itemsselected
          scriteria=scriteria & "," & chr(39) & .itemdata(j) & chr(39)
          next
      else
       msgbox "pls select item from listbox"
       me.lstRptVariety.setfocus
       exit sub
      end if
end with
      scriteria=mid(scriteria,2)

sql = sql & " AND  PRODUCT.PRODUCT_VARIETY_ID In (" & scriteria &")



0
johnnyloffAuthor Commented:
BCUNNEY - Thank you very much!  The problem was that there were the apostrophes around the numbers.  I assume you were using that function for text strings instead of numerical.  So I altered the Function slightly and it worked.  I changed the IF statements to the following:

        If iLoop < (lSize) And Not IsEmpty(vValuesArray(iIndexNextItem%)) Then
            If (bStrings) Then
            'use the following line for TEXT values
            '   BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & "'" & CStr(vValuesArray(iLoop)) & "',"
            'use the following for NUMERICAL values
               BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & CStr(vValuesArray(iLoop)) & ","
            Else
               BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & CStr(vValuesArray(iLoop)) & ","
            End If
        Else
          If Not IsEmpty(vValuesArray(iLoop)) Then
            If (bStrings) Then
            'use the following for TEXT values
            '    BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & "'" & CStr(vValuesArray(iLoop)) & "')"
            'use the following for NUMERICAL values
                 BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & CStr(vValuesArray(iLoop)) & ")"
            Else
                BuildSQL_In_ClauseFromArrayVals = BuildSQL_In_ClauseFromArrayVals & CStr(vValuesArray(iLoop)) & ")"
            End If

Thanks again!  I need this same code in several spots within my DB.
0
johnnyloffAuthor Commented:
JeffTWilley - Thank you for responding.  I do not have any column headers in the list box but I tried the adjustment anyways.  I could not get it to work and I tried several adjustments to no avail.  When it would return info, it would still continue to return the same Product_ID's no matter what was selected.
0
johnnyloffAuthor Commented:
Capricorn1 - Your solution worked after making a small adjustment.  I had the same problem with the apostrophe's.  It appears like yours was set up to return text strings?  I took out the Char39 and it worked.  I modified it as follows:

 For Each j In .ItemsSelected
         ' use the following line for TEXT strings
         ' sCriteria = sCriteria & "," & Chr(39) & .ItemData(j) & Chr(39)
        '  use the following for NUMERICAL strings
          sCriteria = sCriteria & "," & .ItemData(j)
          Next
0
johnnyloffAuthor Commented:
I raised the point value and split points because both solutions worked and I'm sure I will use both of them in various places.  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.