Date syntax error in query expression

[OzNet]

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

[nico5038]

The filtering of the report can be passed by using the WHERE parameter of the DoCmd.OpenReport like:

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,,strFilter 
 
End Sub
 

Nic;o)

[OzNet]

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

[nico5038]

Dates are sometimes mis-interpreted due to the local settings, I always use a format statement:

"Between #" & Format(Me.txtStartDate,"mm-dd-yyyy") & "# And #" & Format(Me.txtEndDate,"mm-dd-yyyy") & "#"
 

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)

[OzNet]

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.

[nico5038]

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)