As Ofer has said you need to use fully qualified references to the controls
on the form as the parameters. With date/time parameters its also advisable
to declare them as such, otherwise a value entered in the form in short date
format can be wrongly interpreted by Access as an arithmetical expression.
This won't cause an error as date/time values are stored as floating point
numbers so the stored number would be compared with the result of the
arithmetical expression, but would give the wrong result. Declaring the
parameters as DateTime also allows for differences in date formatting
internationally.
A further point regarding date ranges is that the BETWEEN….AND operator will
not return any rows where the values in the date field are on the last day of
the range and include a non-zero time of day. There is no such thing as a
date without a time of day in Access, and sometimes non-zero times of day can
be there unseen (a common culprit is the inappropriate use of the Now()
function as a default value). While this can be prevented with a suitable
constraint on the column in the table definition, this is rarely done in my
experience, so its prudent to play safe. This is done by restricting the
result to dates on or after the start date and before the day after the end
date.
So, putting all this together the query would look something like this:
PARAMETERS
Forms!MyForm!StartDate DATETIME,
Forms!MyForm!EndDate DATETIME;
SELECT *
FROM MyTable
WHERE MyDate >= Forms!MyForm!StartDate
AND MyDate < Forms!MyForm!EndDate +1;
In query design view you declare parameters by selecting parameters from the
Query menu and entering the two parameters and their data types in the
dialogue. The range can be defined in design view by entering the following
as the criterion for the MyDate column:
= Forms!MyForm!StartDate AND MyDate < Forms!MyForm!EndDate +1
After you save the query you'll probably find Access has shifted things
round a bit when you reopen it in design view. The end result will be the
same, however.
Ken Sheridan
Stafford, England
bobn3faw said:
My Access 2003 query with criteria to act onstart and end dates for a report:
I created Unbound Form with Unbound Start Date and EndDate. It calls a Macro
which calls a report.
I created Report which has Record Source to Query
I created a Query which has following Criteria:
Between [StartDate] And [EndDate]
Instead of bringing up the Form, it brings up two "Enter Value" windows. I
cannot get it to call the Form.
I have done this many times in prior versions of Access. Will apprecuate
suggestions as to what I may have missed.
(e-mail address removed)
