OpenReport Where Condition with date range

[Guest]

Hi,

I've scoured the forums for an answer to my question, but am wracking my
brain trying to get a Where Condition to work properly.

I want a report to print out records based upon a date range. I have a
button on a form which the user clicks that launches a "report format" form.
The user selects how they want the report to display: all records or a date
range - from the Report Format form. The "all" option works just fine. The
coding of the Where Condition seems to be the problem for the date range
option. The current code (below) opens a blank report that has *Error* where
the data should be. I've tried other varients of quotes placement and the
best I can get it to do is bring up dialog boxes that prompts the user to
enter start date and end date. If I do enter these dates, the report prints
just fine. However, the "Report Format" form and these dialog boxes are
redundant. I just want the "report format" form for entry of start and end
dates.

Thanks for your help!

<code>
Private Sub cmdGetReport_Click()
On Error GoTo Err_cmdGetReport_Click




Dim stDocName As String

stDocName = "Tom's Billable Hours"

If Me.grpRecord.Value = 1 Then

DoCmd.OpenReport stDocName, acViewPreview

ElseIf Me.grpRecord.Value = 2 Then

If IsNull(txtEnterStartDate.Value) Or IsNull(txtEnterEndDate.Value)
Then
MsgBox "You must enter both start and end dates."
DoCmd.GoToControl "txtEnterStartDate"
Exit Sub
Else

If txtEnterStartDate.Value > txtEnterEndDate.Value Then
MsgBox "End date must be greater than Start date."
DoCmd.GoToControl "txtEnterStartDate"
Exit Sub
Else

Me.Visible = False
End If

End If
Dim StartDate As Date
Dim EndDate As Date

StartDate = Me.txtEnterStartDate.Value
EndDate = Me.txtEnterEndDate.Value


DoCmd.OpenReport stDocName, acPreview, , "[Service Date] Between " &
StartDate & " And " & EndDate




End If

Exit_cmdGetReport_Click:
Exit Sub

Err_cmdGetReport_Click:
MsgBox Err.Description
Resume Exit_cmdGetReport_Click

End Sub
</code>

 

[Douglas J. Steele]

Dates need to be delimited with #, and need to be in mm/dd/yyyy format.
(Okay, this isn't strictly true: any unambiguous format, such as yyyy-mm-dd
or dd mmm yyyy will work. The point is, you can't use dd/mm/yyyy format)

DoCmd.OpenReport stDocName, acPreview, , _
"[Service Date] Between " & _
Format(StartDate, "\#mm\/dd\/yyyy\#") & _
" And " & Format(EndDate, "\#mm\/dd\/yyyy\#")

 

[Albert D. Kallal]

change the follwing code

StartDate = Me.txtEnterStartDate.Value
EndDate = Me.txtEnterEndDate.Value
to

StartDate = "#" & format(Me.txtEnterStartDate.Value,"mm/dd/yyyy") & "#"
EndDate = "#" & format(Me.txtEnterEndDate.Value,"mm/dd/yyyy") & "#"

** nice code you have by the way **

 

[Guest]

Doug and Albert -

Thanks so much for your help - and so quick!

I can't take credit for all the code - the part in the elseif section was
snipped from a forum question where somebody else was trying to do something
with date ranges also.

Tom

 

[Guest]

I tried both versions however, and I keep getting "Type Mismatch". Tried
using the format syntax you provided on Service Date to see if that was the
problem, and same error comes up. Any more ideas?

Tom

 

[Allen Browne]

Tom, here's another take on the same thing:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

It's essentially the same thing that Doug and Albert suggested.

If you still get a Type Mismatch, please indicate which line gives this
error.

Also, make sure you compile the code to check it for errors before you run
it. (Compile is on the Debug menu in the code window.)

 

[Guest]

Ok, finally got it to work.

Incorporated some ideas from all of you to end up with the following code -
thanks!

Tom

<final code>
Private Sub cmdGetReport_Click()
On Error GoTo Err_cmdGetReport_Click




Dim stDocName As String

stDocName = "Tom's Billable Hours"

If Me.grpRecord.Value = 1 Then

DoCmd.OpenReport stDocName, acViewPreview

ElseIf Me.grpRecord.Value = 2 Then

If IsNull(txtEnterStartDate.Value) Or IsNull(txtEnterEndDate.Value)
Then
MsgBox "You must enter both start and end dates."
DoCmd.GoToControl "txtEnterStartDate"
Exit Sub
Else

If txtEnterStartDate.Value > txtEnterEndDate.Value Then
MsgBox "End date must be greater than Start date."
DoCmd.GoToControl "txtEnterStartDate"
Exit Sub
Else

Me.Visible = False
End If

End If

Dim StartDate As Date
Dim EndDate As Date
Dim ServDate As String
Const fmtDate = "\#mm\/dd\/yyyy\#"


StartDate = Me.txtEnterStartDate.Value
EndDate = Me.txtEnterEndDate.Value

ServDate = "[Service Date]"


DoCmd.OpenReport stDocName, acPreview, , _
Format(ServDate, fmtDate) & " Between " & _
Format(StartDate, fmtDate) & " And " & _
Format(EndDate, fmtDate)


End If

Exit_cmdGetReport_Click:
Exit Sub

Err_cmdGetReport_Click:
MsgBox Err.Description
Resume Exit_cmdGetReport_Click

End Sub

</final code>