johnnyloff
asked on
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
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(int
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
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_
but it should return this...
((PRODUCT.PRODUCT_VARIETY_
Can anyone help me with the correct way to utilize a multi select box to build a query for a report?
What was working before the change to a multiselect box was:
If Me!lstRptVariety <> 0 Then
sql = sql & " AND PRODUCT.PRODUCT_VARIETY_ID
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(int
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
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_
but it should return this...
((PRODUCT.PRODUCT_VARIETY_
Can anyone help me with the correct way to utilize a multi select box to build a query for a report?
strSQL = "Select * from Product where PRODUCT_VARIETY_ID in(" & myString & ");"
good luck! It's Z-time here.
J
good luck! It's Z-time here.
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
ASKER
BCUNNEY - Well, I couldn't resist trying the code. I tried executing the function but it's giving me an error on vSelectedProductVarieties(
This is how I've used your code.
If CheckIfAnyListBoxItemSelec
sCriteria$ = "WHERE PRODUCT_VARIETY_ID " & BuildSQL_In_ClauseFromArra
sql = sql & " AND PRODUCT_VARIETY_ID = " & sCriteria$
End If
This is how I've used your code.
If CheckIfAnyListBoxItemSelec
sCriteria$ = "WHERE PRODUCT_VARIETY_ID " & BuildSQL_In_ClauseFromArra
sql = sql & " AND PRODUCT_VARIETY_ID = " & sCriteria$
End If
Yo need to declare an array vSelectedProductVarieties(
Dim vSelectedProductVarieties(
Dim vVarietyName() As Variant
Dim lCount&
Dim lstRptVariety As ListBox
Set lstRptVariety = Form.lstRptVariety
Use the above declares before calling the functions
Dim vVarietyName() As Variant
Dim lCount&
Dim lstRptVariety As ListBox
Set lstRptVariety = Form.lstRptVariety
Use the above declares before calling the functions
ASKER
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.
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.
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
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
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
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
you can use this short code to build your where criteria
Dim j , sCriteria
with me.lstRptVariety
end with
Dim j , sCriteria
with me.lstRptVariety
end with
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(iInde
If (bStrings) Then
'use the following line for TEXT values
' BuildSQL_In_ClauseFromArra
'use the following for NUMERICAL values
BuildSQL_In_ClauseFromArra
Else
BuildSQL_In_ClauseFromArra
End If
Else
If Not IsEmpty(vValuesArray(iLoop
If (bStrings) Then
'use the following for TEXT values
' BuildSQL_In_ClauseFromArra
'use the following for NUMERICAL values
BuildSQL_In_ClauseFromArra
Else
BuildSQL_In_ClauseFromArra
End If
Thanks again! I need this same code in several spots within my DB.
If iLoop < (lSize) And Not IsEmpty(vValuesArray(iInde
If (bStrings) Then
'use the following line for TEXT values
' BuildSQL_In_ClauseFromArra
'use the following for NUMERICAL values
BuildSQL_In_ClauseFromArra
Else
BuildSQL_In_ClauseFromArra
End If
Else
If Not IsEmpty(vValuesArray(iLoop
If (bStrings) Then
'use the following for TEXT values
' BuildSQL_In_ClauseFromArra
'use the following for NUMERICAL values
BuildSQL_In_ClauseFromArra
Else
BuildSQL_In_ClauseFromArra
End If
Thanks again! I need this same code in several spots within my DB.
ASKER
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.
ASKER
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
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
ASKER
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!
'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 & ");"