Syntax, Missing Operator

[AnnMV8]

I have a form where I enter the Agent Name, Session Type, Start and End Dates. I’ve done this in the past but not with a Between statement and have not had a problem. I use the code below to put together a statement with the parameters I enter in the form.

I’m not a programmer so I’m not sure what the syntax problem is. I'm hoping someone can help me with this. Thanks in advance.

This is the error I am receiving after entering the four fields:

Syntax error (missing operator) in query expression ‘(txtAgentName= ”Angela Sobczak” AND txtSessionType= ”Coaching Session” AND (dtmSessionDate ) = Between #01/01/2012# AND #02/01/2012#)’

This is the code that puts everything together.

Private Sub cmdApplyFilter_ Click()

Dim strFilter As String
Dim dtmStartDate As Date
Dim dtmEndDate As Date

‘Agent Name
If Not IsNull(Me.cboAg entName) Then
strFilter = strFilter & " AND txtAgentName="" " & Me.cboAgentName & """ "
End If

'Session Type
If Not IsNull(Me.cboSe ssionType) Then
strFilter = strFilter & " AND txtSessionType= """ & Me.cboSessionTy pe & """ "
End If

‘Start and End Dates
If Not (IsNull(Me.dtmS tartDate) Or Me.dtmStartDate = "") Then
strFilter = strFilter & " AND (dtmSessionDate ) = Between #" & Format(Me.dtmSt artDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEn dDate, "mm/dd/yyyy") & "#"
End If

'If the report is closed, open the report
If SysCmd(acSysCmd GetObjectState, acReport, "rptCalls") <> acObjStateOpen Then
DoCmd.OpenRepor t "rptCalls", acPreview
End If

'if report was open, use filter
With Reports![rptCalls]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With

End Sub

[BikeToWork]

AND (dtmSessionDate) = Between #" & Format(Me.dtmStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEndDate, "mm/dd/yyyy") & "#"

I think you've got an extra "=" sign in this statement. I'm not sure that is causing the syntax error but it should be:

AND (dtmSessionDate) Between #" & Format(Me.dtmStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEndDate, "mm/dd/yyyy") & "#"

[AnnMV8]

Thank you so much, that worked.