By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Syntax, Missing Operator

P: 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
Jun 19 '12 #1

✓ answered by BikeToWork

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. AND (dtmSessionDate) Between #" & Format(Me.dtmStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEndDate, "mm/dd/yyyy") & "#"

Share this Question
Share on Google+
2 Replies


100+
P: 124
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. AND (dtmSessionDate) Between #" & Format(Me.dtmStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEndDate, "mm/dd/yyyy") & "#"
Jun 19 '12 #2

P: 23
Thank you so much, that worked.
Jun 19 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.