Multiple selecton in ListBox

[AccessWater]

I create a list box to store a lsit of groups. It allows user to have multiple selections. I also have a "ALL" is the list. On listbox Click event, I then set up the SQL based on the selection. This SQL will be the query for the report.

If user select "ALL", I want all project to be selected. However, my code always show "SELECT * FROM tbFinancial_grouping
WHERE tbFinancial_grouping.RollUp IN('ALL');" Therefore, nothing is selected.

Could anyone et me know what is wrong about my code? Thank you very much.



Private Sub lsRollUp_Click()

Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL1 As String
Set db = CurrentDb()
Set qdf1 = db.QueryDefs("qry_FinancialReport_Selector")


For Each varItem In Me!lsRollUp.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lsRollUp.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If


strCriteria = Right(strCriteria, Len(strCriteria) - 1)

If strCriteria = ALL Then

strSQL1 = "SELECT * FROM tbFinancial_grouping;"

Else

strSQL1 = "SELECT * FROM tbFinancial_grouping " & _
"WHERE tbFinancial_grouping.RollUp IN(" & strCriteria & ");"

End If

qdf1.sql = strSQL1

Set db = Nothing
Set qdf1 = Nothing

End Sub

 

[Bodisathva]

If strCriteria = "ALL" Then

..........

 

[AccessWater]

Hi, Bodisathva, Thank you for your reply. However, changing ALL to "ALL" gave me the same result. I still could not get what I want. Any other idea? Thank you.

 

[ejstefl]

Put a Breakpoint on the line where you are testing to see if strCriteria = "All". See what strCriteria actually is, and go from there.