Problem with limiting a report to a date range when dates are the same

  • Thread starter Thread starter mthornblad
  • Start date Start date
M

mthornblad

Hi

I used Allen Brown's tip for limiting a report to a date range.
http://allenbrowne.com/casu-08.html

It works great and is elegant and flexible. However, I just found
that
if I want to limit the report to a single date and enter the same date
in the start and end date, I get no records even though records exist
for this date.

Please let me know how I can limit the report to just a single date.

Thanks in advance
Mark
 
If the start date and end date are the same, the code should return the
records for just that one date.

If it is not doing that, chances are that your field contains a date as well
as a time. Since that is not an exact match, you need to change to logic to
ask for all the date/time values since midnight today and *before* tomorrow.

If you use Method 1 (parameter query), change the criteria to:
= [StartDate] And < [EndDate] + 1
Click to expand...

If you use Method 2, add 1 to the end date and use less than:

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate + 1,
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 & " >= " &
Format(Me.txtStartDate, conDateFormat) & _
") And (" & strField & " < " & _
Format(Me.txtEndDate + 1, conDateFormat) & ")"
End If
End If
 
If the start date and end date are the same, the code should return the
records for just that one date.

If it is not doing that, chances are that your field contains a date as well
as a time. Since that is not an exact match, you need to change to logic to
ask for all the date/time values since midnight today and *before* tomorrow.

If you use Method 1 (parameter query), change the criteria to:
= [StartDate] And < [EndDate] + 1
Click to expand...

If you use Method 2, add 1 to the end date and use less than:

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate + 1,
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 & " >= " &
Format(Me.txtStartDate, conDateFormat) & _
") And (" & strField & " < " & _
Format(Me.txtEndDate + 1, conDateFormat) & ")"
End If
End If
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




I used Allen Brown's tip for limiting a report to a date range.
http://allenbrowne.com/casu-08.html
Click to expand...
It works great and is elegant and flexible. However, I just found
that
if I want to limit the report to a single date and enter the same date
in the start and end date, I get no records even though records exist
for this date.
Click to expand...
Please let me know how I can limit the report to just a single date.
Click to expand...
Thanks in advance
Mark
Click to expand...
Click to expand...

Thanks Allen

That's my problem. You hit the nail on the head again. I used method
2 and it is wonderful.
I did indeed use date/time. You also answered another question I was
going to ask but you
answered that question also.

Thanks again
Mark
 
Back
Top