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

Date syntax error in query expression

P: 31
Can someone tell me what I am doing wrong here please?

I have a form with two unbound text boxes formatted to short date and an OK button.

My button code is as follows:

Private Sub cmdOK_Click()

Dim strFilter As String

strFilter = "Between #" & Me.txtStartDate.Value & "# And #" & Me.txtEndDate.Value & "#"

'Open Report before applying the filter
DoCmd.OpenReport "rptDisbursementSummaryReport", acViewPreview

With Reports![rptDisbursementSummaryReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

I am getting the following error message:

Syntax error (missing operator) in query expression '(Between #12/12/2007# And #11/12/2008#)'.

Many thanks
Dec 11 '08 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
The filtering of the report can be passed by using the WHERE parameter of the DoCmd.OpenReport like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2.  
  3. Dim strFilter As String
  4.  
  5. strFilter = "Between #" & Me.txtStartDate.Value & "# And #" & Me.txtEndDate.Value & "#"
  6.  
  7. 'Open Report before applying the filter
  8. DoCmd.OpenReport "rptDisbursementSummaryReport", acViewPreview,,strFilter 
  9.  
  10. End Sub
  11.  
Nic;o)
Dec 11 '08 #2

P: 31
Thanks for the advice Nic;o)

However, I am still getting the Syntax error.

I suspect it is something to do with this but I am not sure what is wrong.

strFilter = "Between #" & Me.txtStartDate.Value & "# And #" & Me.txtEndDate.Value & "#"

Thanks
Dec 11 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Dates are sometimes mis-interpreted due to the local settings, I always use a format statement:
Expand|Select|Wrap|Line Numbers
  1. "Between #" & Format(Me.txtStartDate,"mm-dd-yyyy") & "# And #" & Format(Me.txtEndDate,"mm-dd-yyyy") & "#"
  2.  
Oops, also specify which field to test so the statement should look like:

"TableDateField between xxx and yyy"

Just give it a try.

Nic;o)
Dec 11 '08 #4

P: 31
Thanks Nic'o

I tried:
strFilter = "DisbursDate Between #" & Format(Me.txtStartDate.Value, mm - dd - yyyy) & "# And #" & Format(Me.txtEndDate.Value, mm - dd - yyyy) & "#" but I still got the error.

I tried a different approach which is now working:
the OK button code is:
Private Sub cmdOK_Click()

DoCmd.OpenReport "rptDisbursementSummaryReport", acViewPreview
DoCmd.Close acForm, Me.Name

End Sub

In the query field DisbursDate I added:
>=[Forms]![frmMdlDisbursementSummaryReport]![txtStartDate] And <=[Forms]![frmMdlDisbursementSummaryReport]![txtEndDate]

It is doing what I want it to do,

Thanks for your suggestions.
Dec 11 '08 #5

nico5038
Expert 2.5K+
P: 3,072
Your statement holds too many spaces as you have no surrounding quotes in the format statement, it should look like:
Format(Me.txtStartDate,"mm-dd-yyyy")
instead of your:
Format(Me.txtStartDate.Value, mm - dd - yyyy)
(Access now thinks it's a subtraction)

Your solution will work too, but has one small disadvantage. When you rename the form the query will stop working.

Nic;o)
Dec 11 '08 #6

Post your reply

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