Solved

Use Multi Select to build query for a report

Posted on 2007-04-09
17
312 Views
Last Modified: 2009-12-16
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?
0
Comment
Question by:johnnyloff
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
strSQL = "Select * from Product where PRODUCT_VARIETY_ID in(" & myString & ");"

good luck! It's Z-time here.
J
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 250 total points
Comment Utility
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
 

Author Comment

by:johnnyloff
Comment Utility
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
 

Author Comment

by:johnnyloff
Comment Utility
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
 

Author Comment

by:johnnyloff
Comment Utility
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
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
Yo need to declare an array vSelectedProductVarieties() and also lstRptVariety should be declared as Listbox control
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:johnnyloff
Comment Utility
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
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can use this short code to build your where criteria

Dim j , sCriteria

with me.lstRptVariety



end with
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 150 total points
Comment Utility
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
 

Author Comment

by:johnnyloff
Comment Utility
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
 

Author Comment

by:johnnyloff
Comment Utility
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
 

Author Comment

by:johnnyloff
Comment Utility
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
 

Author Comment

by:johnnyloff
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now