Author: Ken SheridanKen Sheridan Date: Sep 22, 2008 23:44
Parameters of date/time data type should always be declared. Otherwise there
is a risk of a value such as 09/22/2008 being interpreted as an arithmetical
expression rather than a date. So in the report's underlying query declare
the parameters like so:
PARAMETERS
Forms!MyForm!txtStartDate DATETIME,
Forms!MyForm!txtEndDate DATETIME;
SELECT *
FROM MyTable
WHERE MyDate BETWEEN
Forms!MyForm!txtStartDate
AND Forms!MyForm!txtEndDate;
Another possible cause of incorrect results is that if any of the values in
the MyDate column contain a non-zero time of day (which can easily be there
without your realizing it if steps have not specifically been taken in the
table definition to disallow such values), then rows with a date and non-zero
time on the final day of the range will not be returned by a BETWEEN….AND
operation. This can be avoided, however, by defining the range differently:
|