Startdate, Enddate

[louonline]

Hi all,
I copied the CODE below from Allen Browne's website. (thanks Allen)
It works Ok except for one small problem.

If the startdate is say 3-Jan-2006 and enddate is 15-Jan-2006 the
report will only return info for 3-Jan-2006 to 14-Jan-2006, NOT
15-JAN-2006.

To get info up to 15-Jan-2006 I have to enter 16-Jan-2006 as the
enddate.

I can live with this because I know how it works but I would like to
have a "what-you-see-is-what-you-get" for other users.

Is there any way to fix this so it will include info for the ENTERED
enddate (15-Jan-2006)?

Regards,
Lou
---------------
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"


strReport = "Contingency Report"
strField = "Closing Date"


If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtenddate,
conDateFormat)
End If
Else
If IsNull(Me.txtenddate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtstartdate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtstartdate,

conDateFormat) _
& " And " & Format(Me.txtenddate, conDateFormat)
End If
End If


' Debug.Print strWhere 'For debugging purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

 

[Rick Brandt]

Hi all,
I copied the CODE below from Allen Browne's website. (thanks Allen)
It works Ok except for one small problem.

If the startdate is say 3-Jan-2006 and enddate is 15-Jan-2006 the
report will only return info for 3-Jan-2006 to 14-Jan-2006, NOT
15-JAN-2006.

To get info up to 15-Jan-2006 I have to enter 16-Jan-2006 as the
enddate.

I can live with this because I know how it works but I would like to
have a "what-you-see-is-what-you-get" for other users.

Is there any way to fix this so it will include info for the ENTERED
enddate (15-Jan-2006)?

This is because your data contains time as well as date data. When you
specify....

BETWEEN #3-Jan-2006# AND #15-Jan-2006#

....you are actually specifying...

BETWEEN #3-Jan-2006 12:00:00 AM# AND #15-Jan-2006 12:00:00 AM#

....because Access DateTimes ALWAYS have a full date and time. Midnight is just
assumed when no time is specified.

You should be able to see why this would result in no records returned for the
last day and adding an additional day to the EndDate is the best way to deal
with this. To modify the code to do this for you change the code as below.

---------------
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Contingency Report"
strField = "Closing Date"

If IsNull(Me.txtstartdate) Then
If Not IsNull(Me.txtenddate) Then 'End date, but no start.
strWhere = strField & " < " _
& Format(DateAdd("d", 1, Me.txtenddate), conDateFormat)
End If
Else
If IsNull(Me.txtenddate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtstartdate,conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " &
Format(Me.txtstartdate,conDateFormat) _
& " And " & Format(DateAdd("d", 1, Me.txtenddate),
conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

 

[louonline]

Thanks Rick, that did the trick.
Regards,
Lou