SQL Syntax Error

[Purdue2479]

The below code is giving the attached error after running the first time. The user enters a date range, selects a carrier and client from list boxes,
and the query pulls back the data in datasheet view.

Option Compare Database
Option Explicit

    Dim db As Database
    Dim qdf As QueryDef
    Dim SDate, EDate
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strCriteria2 As String
    Dim strSQL As String
    Dim ByClient As Boolean

Private Sub cmdOK_Click()
On Error GoTo Error_Handling

    DoCmd.Hourglass True
    ByClient = ChkClient.Value
    SDate = txtStartDate.Value
    EDate = txtEndDate.Value
 
 Call SetQuery
 DoCmd.OpenQuery "qEDI_Compliance", acNormal, acReadOnly
 
DoCmd.Hourglass False
    Exit Sub
    
Exit_cmdOK_Click:
    Exit Sub
 
Error_Handling:

   MsgBox Err.Description
   DoCmd.Hourglass False
   Resume Exit_cmdOK_Click
   
End Sub
 Public Function SetQuery()
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qEDI_Compliance")

    If ByClient = True Then
    ' Loop through the selected items in the list box and build a text string
        If Me!lstClients.ItemsSelected.Count > 0 And Me!lstCarriers.ItemsSelected.Count > 0 Then
            For Each varItem In Me!lstClients.ItemsSelected
            strCriteria2 = strCriteria2 & "Clients.Cust_Key = " & Chr(34) _
                          & Me!lstClients.ItemData(varItem) & Chr(34) & "OR "
            Next varItem
            strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
            
            For Each varItem In Me!lstCarriers.ItemsSelected
            strCriteria = strCriteria & "INV_BAT.Vend_Labl = " & Chr(34) _
                          & Me!lstCarriers.ItemData(varItem) & Chr(34) & "OR "
         Next varItem
          strCriteria = Left(strCriteria, Len(strCriteria) - 3)
         
         
         ElseIf Me!lstCarriers.ItemsSelected.Count = 0 And Me!lstClients.ItemsSelected.Count > 0 Then
           For Each varItem In Me!lstClients.ItemsSelected
            strCriteria2 = strCriteria2 & "Clients.Cust_Key = " & Chr(34) _
                          & Me!lstClients.ItemData(varItem) & Chr(34) & "OR "
            Next varItem
            strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
         
         strCriteria = "INV_BAT.Vend_Labl Like '*'"
        
       Else
         strCriteria = "INV_BAT.Vend_Labl Like '*'"
         strCriteria2 = "Clients.Cust_Key Like'*'"
         
     End If
        
  End If
  
  If ByClient = True Then
    strSQL = "SELECT Clients.[Client Name], INV_BAT.VEND_LABL, Vendor.VEND_NAME, INV_BAT.BAT_TYPE,  Count(AR_DTL_FB.DTL_ID) AS CountOfDTL_ID " & _
    "FROM (((INV_BAT INNER JOIN FRGHT_BL ON INV_BAT.BAT_ID = FRGHT_BL.BAT_ID) INNER JOIN Vendor ON INV_BAT.VEND_LABL = Vendor.VEND_LABL) INNER JOIN AR_DTL_FB ON FRGHT_BL.FB_ID = AR_DTL_FB.FB_ID) INNER JOIN Clients ON AR_DTL_FB.CUST_KEY = Clients.CUST_KEY " & _
    "WHERE (((DateValue([INV_BAT].[BAT_CREAT_DTM])) Between #" & SDate & "# And #" & EDate & "#) AND (" & strCriteria2 & ") AND (" & strCriteria & "))" & _
    "GROUP BY Clients.[Client Name], INV_BAT.VEND_LABL, Vendor.VEND_NAME, INV_BAT.BAT_TYPE " & _
    "ORDER BY INV_BAT.VEND_LABL;"
  
  End If
  
If ByClient = False Then
  
   If Me!lstCarriers.ItemsSelected.Count > 0 And Me!lstClients.ItemsSelected.Count = 0 Then
           For Each varItem In Me!lstCarriers.ItemsSelected
            strCriteria = strCriteria & "INV_BAT.Vend_Labl = " & Chr(34) _
                          & Me!lstCarriers.ItemData(varItem) & Chr(34) & "OR "
         Next varItem
          strCriteria = Left(strCriteria, Len(strCriteria) - 3)
                 
    strSQL = "SELECT INV_BAT.VEND_LABL, Vendor.VEND_NAME, INV_BAT.BAT_TYPE, Sum(INV_BAT.BAT_FB_CNT) AS SumOfBAT_FB_CNT " & _
    "FROM INV_BAT INNER JOIN Vendor ON INV_BAT.VEND_LABL = Vendor.VEND_LABL " & _
    "WHERE (((DateValue(INV_BAT.BAT_CREAT_DTM))Between #" & SDate & "# And #" & EDate & "#) And " & strCriteria & " )" & _
    "GROUP BY INV_BAT.VEND_LABL, Vendor.VEND_NAME, INV_BAT.BAT_TYPE " & _
    "ORDER BY INV_BAT.VEND_LABL; "

    End If
 
 End If
  
 qdf.sql = strSQL
 db.QueryDefs.Refresh
 qdf.Close
 Set qdf = Nothing
 db.Close
 Set db = Nothing
    
Exit Function

End Function

 

[Bat17]

At a guess, it is because you are declareing your variables globaly. first time round it will work OK but second time strCriteria2 still has the original value stored and you are adding to thet. Either move the variable inside their function or set them to empty strings at the start of the function.

HTH

Peter

 

[Purdue2479]

That worked. Thanks.