Need Help building SQL from Multiple Selections in Listbox

[mystuff]

I have a list box populated from a table. I want the user to select one or more values in the listbox and build an SQL statement with the selected items. The Multi Select is set to "Simple"

So far, this is what I have in my code. I pass in the name of the list control and the fieldname. But when the 1st line after the "For" statement is exeucted, I get the error message "Object doesn't support this property or Method" Does anyone have any idea why? Or any idea how to capture the selected items in the list box?

Function fncAddSQL(ctlList As Control, strFieldName As String)

Dim varItem As Variant
Dim strNewSQL As String
Dim strValue As String

For Each varItem In ctlList.ItemsSelected
strValue = ctlList.ItemdData(varItem)
strNewSQL = "AND [" & strFieldName & "] = '" & strValue & "' "
Next varItem


End Function

After this is run, I will add the strNewSQL to my Where clause, but I can't get this part to work.

 

[lespaul]

first off I can see that each time you loop through you are replacing the value of strNewSQL, you are NOT adding the "AND FIELDNAME" to the end of the string, you are REPLACING it each time:

Function fncAddSQL(ctlList As Control, strFieldName As String)

    Dim varItem As Variant
    Dim strNewSQL As String
    Dim strValue As String

    For Each varItem In ctlList.ItemsSelected
        strValue = ctlList.ItemdData(varItem)
        if strNewSQL = "" then
          strNewSQL = "[" & strFieldName & "] = '" & strValue & "' "
         else strNewSQL = strNewSQL & " AND [" & strFieldName & "] = '" & strValue & "' "
        end if

    Next varItem
    

End Function

Another option would be to make it an IN clause:

WHERE FIELDNAME IN ('VALUE1', 'VALUE2', 'VALUE3')



Leslie

 

[PHV]

Object doesn't support this property or Method
ItemdData should be replaced by ItemData
You may try something like this:
Function fncAddSQL(ctlList As Control, strFieldName As String) As String
Dim varItem As Variant
Dim strNewSQL As String
Dim strValue As String

strNewSQL = " AND [" & strFieldName & "] IN ("
For Each varItem In ctlList.ItemsSelected
strValue = strValue & ",'" & ctlList.ItemData(varItem) & "'"
Next varItem
fncAddSQL = strNewSQL & Mid(strValue, 2) & ")"
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mystuff]

Thank you both. It is now working.

One more question. I would like the user to be able to press a button (called RESET) which will clear out the list box. Is there an easy way to erase all selected items in a list box?

Thanks.

 

[PHV]

Something like this ?
With Me.[ListBox name]
For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244