23
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.cboAgentName) Then
strFilter = strFilter & " AND txtAgentName=""" & Me.cboAgentName & """ "
End If
'Session Type
If Not IsNull(Me.cboSessionType) Then
strFilter = strFilter & " AND txtSessionType=""" & Me.cboSessionType & """ "
End If
‘Start and End Dates
If Not (IsNull(Me.dtmStartDate) Or Me.dtmStartDate = "") Then
strFilter = strFilter & " AND (dtmSessionDate) = Between #" & Format(Me.dtmStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEndDate, "mm/dd/yyyy") & "#"
End If
'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport, "rptCalls") <> acObjStateOpen Then
DoCmd.OpenReport "rptCalls", acPreview
End If
'if report was open, use filter
With Reports![rptCalls]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End Sub
