parameter query based on Date range?

[Guest]

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

 

[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.

 

[Guest]

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.

 

[Jamie Collins]

I would always do so myself even
if I'm confident that the date/time values all have zero times of day

Well, you can be 100% confident that no dates have a time element if
you code the rule i.e. by creating a CHECK constraint (or Validation
Rule) e.g.

CREATE TABLE Test (
test_ID INTEGER NOT NULL PRIMARY KEY,
start_date DATETIME DEFAULT DATE() NOT NULL,
CONSTRAINT test_start_date__no_time_element
CHECK(start_date = CDATE(DATEDIFF('d', 0, start_date))),
end_date DATETIME,
CONSTRAINT test_end_date__no_time_element
CHECK(end_date = CDATE(DATEDIFF('d', 0, IIF(end_date IS NULL, DATE(),
end_date))))
);

An attempt to insert a date value with a time element will always fail
e.g.

INSERT INTO Test (test_ID, start_date) VALUES (1, NOW());

Error: "One or more values are prohibited by the validation rule
'test_start_date__no_time_element' set for 'Test'. Enter a value that
the expression for this field can accept."

The presence of a CHECK rule means you can be 100% confident and you
can code accordingly.

[PS to make the above code more friendly to use, create a procedure to
*handle* dates with time elements (i.e. strip time elements before
inserting) e.g.

CREATE PROCEDURE AddTest
test_ID INTEGER,
:start_date DATETIME,
:end_date DATETIME = NULL)
AS
INSERT INTO Test
(test_ID, start_date, end_date)
VALUES
test_ID,
CDATE(DATEDIFF('d', 0, IIFstart_date IS NULL, DATE(), :start_date))),
IIFend_date IS NULL, NULL, CDATE(DATEDIFF('d', 0, :end_date)))
);

A similar proc may be required to update the table. Now remove
permissions from the base table and grant EXCUTE permissions on the
procs.]

using the following in place of a BETWEEN....AND operation:

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

The drawback of this approach is that you are effectively using two
lines of code to write one search condition. The OP's preference for
the BETWEEN syntax backs the case that it is more intuitive in that it
is more human readable, but the drawback of this is that the data may
not fit the logic. As above, the data can be made to fit by coding the
business rule that end dates will always contain the maximum value for
the time element, which for Jet's DATETIME type is 23:59:59:

CREATE TABLE Test2 (
test_ID INTEGER NOT NULL PRIMARY KEY,
start_date DATETIME DEFAULT DATE() NOT NULL,
CONSTRAINT start_date__no_time_element
CHECK(start_date = CDATE(DATEDIFF('d', 0, start_date))),
end_date DATETIME,
CONSTRAINT end_date__no_time_element
CHECK(end_date = CDATE(DATEDIFF('d', 0, IIF(end_date IS NULL, DATE(),
end_date))) + TIMESERIAL(23, 59, 59))
);

Having coded the rule, the construct

:search_date BETWEEN start_date AND end_date

is intuitive and meaningful.

Jamie.

--