parameter query based on Date range?

J

JPol

How to setup Parameter Query based on Date range?

Field in the Query is [School_Date]. I would like to have parameter query
that would give me data according to Between #?date# AND #?date# . How do I
setup the Parameter Box and Criteria box under the [School-Date] field.
Appreciate Step by Step. I am new to this.
JPol
 
W

Wayne Morgan

In query design view, type the following for the criteria under the
[School_Date] field:

Between [Enter Start Date] And [Enter End Date]

If you have a form to supply this, replace the two parameters with the path
to the controls on the form.

The next step is to tell the query that the data type of the parameters is
Date/Time. To do this, with the query open in design view go to
Query|Parameters... on the menu bar. In the resulting dialog box enter the
two parameters exactly as they are in the criteria box of the query under
the Parameter column of the dialog box and choose Date/Time under the Data
Type column.

Example:
[Enter Start Date] Date/Time
[Enter End Date] Date/Time

Click Ok to close the dialog box.
 
K

Ken Sheridan

When working with date ranges the BETWEEN….AND operator can sometimes be
unreliable as it will only return rows with a value on the final day of the
range if the date/time value has a zero time-of-day element, i.e. at midnight
at the start of the day. This will be the case if the dates have been
entered as dates but sometimes a non-zero time of day can creep in without
you being aware of it or the values might have been entered with a time of
day for a valid reason. A common culprit for unseen times of day is the
inappropriate use of the Now() function as a default value. Its easy to
avoid being caught out by this, however, and I would always do so myself even
if I'm confident that the date/time values all have zero times of day, by
using the following in place of a BETWEEN….AND operation:

WHERE School_Date >= [Enter Start Date:] AND School_Date < [Enter End Date]
+ 1

This defines the end of the range as before the start of the day one day
after the final day, so will pick up all date/time values on the final day
regardless of what time of day element the values might have. Incidentally
there is no such thing in Access as date value without a time of day or vice
versa. If you enter a date without a time you are entering a date/time value
at midnight at the start of the day. If you enter a time without a date you
are actually entering a value of that time on 30 December 1899, which is day
zero for Access, date/time values being stored as 64 bit floating pint
numbers as an offset from midnight at the start of that date.
 
Top