I have just discovered that one of my reports is not capturing data for the end
ID: 3560378 • Letter: I
Question
I have just discovered that one of my reports is not capturing data for the end date specified. It does not matter which of the two methods I use below.
Between [Forms]![frm_ReportDateRange]![BeginDate] And [Forms]![frm_ReportDateRange]![EndDate]
>=[Forms]![frm_ReportDateRange]![BeginDate] And <=[Forms]![frm_ReportDateRange]![EndDate]
In the end, to get this report to work properly, I had to account for this problem in the following way, simply add an extra day to capture the day before.
Between [Forms]![frm_ReportDateRange]![BeginDate] And [Forms]![frm_ReportDateRange]![EndDate]+1
If anyone is aware of a bug, or can shed some light on this issue, I would appreciate it. Thanks.
Explanation / Answer
Not a bug, just poor data. As it stands you should use this method of defining a range:
WHERE [YourDateField] >= [Forms]![frm_ReportDateRange]![BeginDate]
AND [YourDateField] < [Forms]![frm_ReportDateRange]![EndDate]+1
Your solution will only work if every date has a non-zero time of day element. You should also declare the parameters to avoid their being misinterpreted as arithmetical expressions, which you can do via the parameters dialogue in query design view or by adding a PARAMETERS clause to the start of the query IN SQL view:
PARAMETERS [Forms]![frm_ReportDateRange]![BeginDate] DATETIME,
[Forms]![frm_ReportDateRange]![EndDate] DATETIME;
SELECT etc........
However, the real solution is to eliminate values with a non-zero time of day element from the column with the following 'update' query:
UPDATE [YourTable]
SET [YourDateField] = DATEVALUE([YourDateField])
WHERE [YourDateField] IS NOT NULL;
Then disallow values with a non-zero time of day element by applying the following expression to the column's ValidationRule property:
Is Null Or DateValue([YourDateField])
If you have used the Now() function to set a default value for the column, change it to the Date() function.