Laurie:
The SQL I sent you is just an example and yours will differ. You can see
yours by opening the query in design view and switching to SQL view. It
should start:
PARAMETERS [Forms]![frmReports1]![txtStartDate6] DateTime,
[Forms]![frmReports1]![txtStartDate6] DateTime;
SELECT…..
If not, add the parameters clause as above, switch back to design view and
save the query.
From what you say it sounds like you've entered the references to the form's
controls on two separate criteria lines of the design grid. When you enter
criteria on separate lines it’s the equivalent of an OR operation, so it
would find rows on either of the dates but not on the intervening dates.
What you should do is:
1. Delete both criteria first.
2. In the first criteria row only put, all as one line:
= [Forms]![frmReports1]![txtStartDate6] And [YourdateField] < DateAdd("d",1, [Forms]![frmReports1]![txtEndDate6])
substituting the real name of the date field for YourDateField in the above
example.
3. Save the query.
All being well the report should now return records for any date in the
range defined by the dates you enter in the form.
If you still have problems open your query in SQL view again and copy the
whole of the SQL to the clipboard, and then paste it into your message here.
One thing you'll notice if you open the query in design view after making
the changes described above is that Access will have moved things around
somewhat. Don't worry, it should work exactly the same.
Ken Sheridan
Stafford, England
AlbertaRose said:
Yes, this is what I have existing in my query criteria:
[Forms]![frmReports1]![txtStartDate6]
[Forms]![frmReports1]![txtEndDate6]
The parameters have been declared as date/time correctly.
Where would the following be entered?
SELECT *
FROM MyTable
WHERE MyDate BETWEEN
Forms!MyForm!txtStartDate
AND Forms!MyForm!txtEndDate;
Thanks...
Laurie
Ken Sheridan said:
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:
PARAMETERS
Forms!MyForm!txtStartDate DATETIME,
Forms!MyForm!txtEndDate DATETIME;
SELECT *
FROM MyTable
WHERE MyDate >= Forms!MyForm!txtStartDate
AND MyDate < DATEADD("d",1,Forms!MyForm!txtEndDate);
This looks for values after the start of the first date and before the start
of one day after the end date, so dates on the final day will be picked up
regardless of their time of day element.
Ken Sheridan
Stafford, England
