Solved

Use Multi Select to build query for a report

Posted on 2007-04-09
17
352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18880701
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
ID: 18880706
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
ID: 18880830
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:johnnyloff
ID: 18880863
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
ID: 18880875
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
ID: 18880949
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
ID: 18881004
Yo need to declare an array vSelectedProductVarieties() and also lstRptVariety should be declared as Listbox control
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 18881018
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
 

Author Comment

by:johnnyloff
ID: 18881096
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
ID: 18881481
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
ID: 18881513
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18881654
you can use this short code to build your where criteria

Dim j , sCriteria

with me.lstRptVariety



end with
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 150 total points
ID: 18881701
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
ID: 18885159
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
ID: 18885172
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
ID: 18885197
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
ID: 18885200
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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