Dear Experts, i want to join two criteria in vba to make one strcriteria which will be feeded to query to get the result my code is as below. if i test one by one criteria then its working how can i combile the both criterias
my vba code is
Private Sub cmdApplyFilter_Click()
'Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim StrSite As String
Dim StrSubconnature As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Query4")
' Loop through the selected items in the list box and build a text string
If Me!lstSite.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstSite.ItemsSelected
StrSite = StrSite & "Query2.Sitecode = " & Chr(34) _
& Me!lstSite.ItemData(varItem) & Chr(34) & "OR "
Next varItem
StrSite = Left(StrSite, Len(StrSite) - 3)
Else
StrSite = "Query2.Sitecode Like '*'"
End If
If Me!lstSubconnature.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstSubconnature.ItemsSelected
StrSubconnature = StrSubconnature & "Query2.nature = " & Chr(34) _
& Me!lstSubconnature.ItemData(varItem) & Chr(34) & "OR "
Next varItem
StrSubconnature = Left(StrSubconnature, Len(StrSubconnature) - 3)
Else
StrSubconnature = "Query2.nature Like '*'"
End If
strCriteria = StrSite & Strsubconnature
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Query2 " & _
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.Close acForm, "frmReportFilter3"
DoCmd.OpenReport "Rpt_SubconBreakdown", acViewPreview, "", "", acNormal
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub