Problem with date range for single day

[Guest]

Access03/WinXP

On a form there is a Start Date and End Date, both form fields formatted as
Short Date. In the query, the field DatePaid has criteria >=
forms!frmPaymentsSummary.txtStartDate AND <=
forms!frmPaymentsSummary.txtEndDate.

When I run the query with a date range of a single day (to get all payments
processed on that day), only one record is coming up. If I run the date
range on that day and ending on the next, I get all of the records processed
on that day. For example, today 142 records were processed. If I run the
date range of 8/8/07 to 8/8/07, then I get the one record. If I run the
range from 8/8/07 to 8/9/07, then I get the 142 records.

Any ideas?

 

[Douglas J. Steele]

Sounds as though DatePaid includes time, not just date. (Are you using the
Now function to populate it?)

Try using a criteria of

= forms!frmPaymentsSummary.txtStartDate AND <= DateAdd("d", 1,

forms!frmPaymentsSummary.txtEndDate)

 

[fredg]

Access03/WinXP

On a form there is a Start Date and End Date, both form fields formatted as
Short Date. In the query, the field DatePaid has criteria >=
forms!frmPaymentsSummary.txtStartDate AND <=
forms!frmPaymentsSummary.txtEndDate.

When I run the query with a date range of a single day (to get all payments
processed on that day), only one record is coming up. If I run the date
range on that day and ending on the next, I get all of the records processed
on that day. For example, today 142 records were processed. If I run the
date range of 8/8/07 to 8/8/07, then I get the one record. If I run the
range from 8/8/07 to 8/9/07, then I get the 142 records.

Any ideas?

Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = DateValue([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().

 

[Guest]

Thanks very much to both Doug and Fred.

Fred, I did run your update query just in case there were still records with
any kind of time stamp on them. After the update, my query still only
displayed the single record.

I believe I now understand the implications of your other comment; do all
date fields have a time stamp but if not specified the time stamp is always
12:00:00 AM? I'm trying to figure out why, after removing all time stamp
references in the data, field design, form design and query specs, I still
only get the one record. Would not >=8/8/07 and <=8/8/07 give me all records
after these updates?

In using Doug's DateAdd function, I do in fact get all the records. This
leads me to believe that records with no time stamp, supposedly, actually do
have the time stamp of 12:00:00 AM and therefore to have the day's records,
you must query 8/8/07 12:00:00 AM through 8/9/07 12:00:00 AM. My first
reaction to Doug's addition was to ask why this didn't display records from
8/9/07 as well as to those of 8/8/07.

Seems counterintuitive to me, but then maybe I've just been staring at
numbers too long today.

 

[Douglas J. Steele]

Yes, all Dates have a time of 12:00:00 AM.

Under the covers, a Date data field is an 8 byte floating point number,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day. A date with no time, therefore, has 0 as the time, which is
12:00:00 AM.

If you had records that strictly contained 8/9/07 as the date, then my
approach would pick them up as well. Change it to

< DateAdd("d", 1, forms!frmPaymentsSummary.txtEndDate)

to get around that.

 

[fredg]

Thanks very much to both Doug and Fred.

Fred, I did run your update query just in case there were still records with
any kind of time stamp on them. After the update, my query still only
displayed the single record.

Are you sure that none of the records have a Null value?
I would suggest you run a Select query on your date field.
Exp:Format([DateField],"mm/dd/yyyy hh:nn:ss")

The above will display your date including the time value. If all show
the time value as 00:00:00 (midnight) then the time is not causing
your query to miss records.

I believe I now understand the implications of your other comment; do all
date fields have a time stamp but if not specified the time stamp is always
12:00:00 AM? I'm trying to figure out why, after removing all time stamp
references in the data, field design, form design and query specs, I still
only get the one record. Would not >=8/8/07 and <=8/8/07 give me all records
after these updates?

In using Doug's DateAdd function, I do in fact get all the records. This
leads me to believe that records with no time stamp, supposedly, actually do
have the time stamp of 12:00:00 AM and therefore to have the day's records,
you must query 8/8/07 12:00:00 AM through 8/9/07 12:00:00 AM. My first
reaction to Doug's addition was to ask why this didn't display records from
8/9/07 as well as to those of 8/8/07.

Seems counterintuitive to me, but then maybe I've just been staring at
numbers too long today.

*** snipped ***

Records entered using Now() include the date and the time. The time
portion is entered as a percentage of the 24 hour day since midnight.
Midnight (which by the way in Access is 00:00:00 AM , NOT 24:00:00 AM)
is stored as .0, 6:00 AM is stored as .25 (a fourth of a day), 6:00 PM
is stored as 0.75 (3/4 of a day), etc.
The day portion is stored as the number of days since 12/30/1899

This moment is stored as 39302.5990972222
which formated as date and time is
8/8/2007 2:22:42 PM

Records entered using Date() include the date and a time value of
Midnight .0
Today's date, entered using date is:
39302.0

I hope this helps.

 

[Guest]

Thanks for the lesson - I always appreciate learning these details. I'm not
quite sure what you mean by "strictly contained" - if I am using Short Date
and ran Fred's query to strip the time stamp, would not the records strictly
contain only the date, or is the case you describe of having the date and 0
as the time?

I did in fact incorporate your addition and the query works well.

Interestingly enough, this query is set on one payments table (Table A)
which is very similar to another payments table (Table B - for a different
module in the database). Yet the payments query for Table B will yield two
days' payments if I enter in two dates which are numerically one number
apart. I would swear up and down that the payment date field in each table
is exactly the same format and the user input forms are exactly the same
format with respect to the payment date field. For both tables, I even ran
Fred's update query and yet still get different results with respect to the
payment dates being returned.

I think I'm confused enough for the day.....

Thanks again!

 

[Guest]

I would not make a good gambler, because I say that I would bet money on
something and I keep losing.

I know that I ran my update query as you said, but then ran the query
displaying the time as you suggested. There were hundreds of records with a
time stamp other than 00:00:00 (albeit well out of the present day date range
employed in the query). So I ran your update query again, removed the
DateAdd function from the query, and using 8/8/07 I get all of the records.

Now I know it's really time to go home.

Thanks very much to both Doug and Fred.

Fred, I did run your update query just in case there were still records with
any kind of time stamp on them. After the update, my query still only
displayed the single record.

Are you sure that none of the records have a Null value?
I would suggest you run a Select query on your date field.
Exp:Format([DateField],"mm/dd/yyyy hh:nn:ss")

The above will display your date including the time value. If all show
the time value as 00:00:00 (midnight) then the time is not causing
your query to miss records.

I believe I now understand the implications of your other comment; do all
date fields have a time stamp but if not specified the time stamp is always
12:00:00 AM? I'm trying to figure out why, after removing all time stamp
references in the data, field design, form design and query specs, I still
only get the one record. Would not >=8/8/07 and <=8/8/07 give me all records
after these updates?

In using Doug's DateAdd function, I do in fact get all the records. This
leads me to believe that records with no time stamp, supposedly, actually do
have the time stamp of 12:00:00 AM and therefore to have the day's records,
you must query 8/8/07 12:00:00 AM through 8/9/07 12:00:00 AM. My first
reaction to Doug's addition was to ask why this didn't display records from
8/9/07 as well as to those of 8/8/07.

Seems counterintuitive to me, but then maybe I've just been staring at
numbers too long today.

*** snipped ***

Records entered using Now() include the date and the time. The time
portion is entered as a percentage of the 24 hour day since midnight.
Midnight (which by the way in Access is 00:00:00 AM , NOT 24:00:00 AM)
is stored as .0, 6:00 AM is stored as .25 (a fourth of a day), 6:00 PM
is stored as 0.75 (3/4 of a day), etc.
The day portion is stored as the number of days since 12/30/1899

This moment is stored as 39302.5990972222
which formated as date and time is
8/8/2007 2:22:42 PM

Records entered using Date() include the date and a time value of
Midnight .0
Today's date, entered using date is:
39302.0

I hope this helps.

 

[Jamie Collins]

all Dates have a time of 12:00:00 AM.

Under the covers, a Date data field is an 8 byte floating point number,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and thedecimalportion represents the time as a fraction
of a day. A date with no time, therefore, has 0 as the time, which is
12:00:00 AM.

Another approach: Access/Jet has but one temporal data type named
DATETIME and values will always have a 'date' and a 'time' element;
the 'time' element will only be midnight if you have constrained it to
be so. You won't go far wrong if you always use DATETIME functionality
e.g. DATEDIFF('D', start_date, end_date) rather than (start_date -
end_date) etc. Knowledge of their floating point natural may lead to
good things (e.g. modelling periods using closed-open representation
with WHERE start_date <= target_date AND target_date < end_date, even
though closed-closed representation with WHERE target_date BETWEEN
start_date AND end_date is better optimized for the Access/Jet engine)
but usually it leads to IMO bad ("So Access/Jet supports sub-second
DATETIME values then...?")

Jamie.

--

 

[Jamie Collins]

On a form there is a Start Date and End Date, both form fields formatted as
Short Date. In the query, the field DatePaid has criteria >=
forms!frmPaymentsSummary.txtStartDate AND <=
forms!frmPaymentsSummary.txtEndDate.

When I run the query with a date range of a single day (to get all payments
processed on that day), only one record is coming up. If I run the date
range on that day and ending on the next, I get all of the records processed
on that day. For example, today 142 records were processed. If I run the
date range of 8/8/07 to 8/8/07, then I get the one record. If I run the
range from 8/8/07 to 8/9/07, then I get the 142 records.

Any ideas?

Consider these examples (substitute the name of a populated table for
'MyTable' in the queries).

Example 1: use closed-closed representation (assumes DATETIME values
are accurate to one second granularity) and BETWEEN which is optimized
for Access/Jet:

SELECT DT1.test_date
FROM
(
SELECT DISTINCT #2001-01-01 00:00:00# AS test_date
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-01 12:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-01 23:59:59#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 00:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 12:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 23:59:59#
FROM MyTable
) AS DT1
WHERE DT1.test_date BETWEEN #2001-01-01 00:00:00# AND #2001-01-01
23:59:59#

Example 2: use closed-open representation which is good for DATETIME's
floating point nature (i.e. no assumption one second granularity):

SELECT DT1.test_date
FROM
(
SELECT DISTINCT #2001-01-01 00:00:00# AS test_date
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-01 12:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-01 23:59:59#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 00:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 12:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 23:59:59#
FROM MyTable
) AS DT1
WHERE #2001-01-01 00:00:00# <= DT1.test_date
AND DT1.test_date < #2001-01-02 00:00:00#;

Example 3: the OP's original, which erroneously returns values for the
next day:

SELECT DT1.test_date
FROM
(
SELECT DISTINCT #2001-01-01 00:00:00# AS test_date
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-01 12:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-01 23:59:59#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 00:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 12:00:00#
FROM MyTable
UNION ALL
SELECT DISTINCT #2001-01-02 23:59:59#
FROM MyTable
) AS DT1
WHERE DT1.test_date >= #2001-01-01 00:00:00#
AND DT1.test_date <= #2001-01-02 00:00:00#;

I use the Example 1 approach because BETWEEN is more human readable
(being optimized is a bonus) and I find open-open representation
(where the end date falls outside the period) non-intuitive. Who
knows, maybe the floating point DATETIME storage will one day be
replaced by scaled integers as per SQL Server (breath not being held
<g>) so why code based on current physical implementation
characteristics?

Jamie.

--