Trouble with "BETWEEN #time# AND #time#"

[Guest]

I have a user trying to query by the time field between two times. Her time
criteria are "Between #11:01:00 PM# And #6:59:00 AM#". This results in times
outside the time range, e.g., 9:00 PM, 8:00 AM, etc.

Here's the query at its simplest:

SELECT [Time Table].[Time of Day]
FROM [Time Table]
WHERE ((([Time Table].[Time of Day]) Between #12/30/1899 23:1:0# And
#12/30/1899 6:59:0#));

What is going wrong here? Does Access not "see" times spanning midnight as
within the same time range, e.g., from 11pm to 7am?

TIA,
Steve Vincent
(e-mail address removed)

 

[Guest]

Chris, thank you for pointing out that obvious issue -- it's earlier in the
same day! I had created a sample table to work on this issue, and with the
date/time data type, I chose the "short time" format. I didn't realize until
viewing the SQL that Access entered what I'm assuming is day "1" as a bogus
date placeholder.

Now I'm wondering if there's any way to extract only "time of day" between
those two times, regardless of the date? I might have to part out the
date/time field for that, eh?

I have a user trying to query by the time field between two times. Her time
criteria are "Between #11:01:00 PM# And #6:59:00 AM#". This results in times
outside the time range, e.g., 9:00 PM, 8:00 AM, etc.

Here's the query at its simplest:

SELECT [Time Table].[Time of Day]
FROM [Time Table]
WHERE ((([Time Table].[Time of Day]) Between #12/30/1899 23:1:0# And
#12/30/1899 6:59:0#));

What is going wrong here? Does Access not "see" times spanning midnight as
within the same time range, e.g., from 11pm to 7am?

TIA,
Steve Vincent

Steve Vincent,

My first thought may be the most irrelevant, but from the above it
appears you have test dates in the 19th Century.


In any event, in this time-related case, "BETWEEN" has a start time
and an end time.

The code you have given above has an end time that is earlier than
the start time.

Start Time: #12/30/1899 23:1:0#

Is after:

End Time: #12/30/1899 6:59:0#

Normally, this should not happen. The end time should always be
later than the start time.

Perhaps the End Time should be: #12/31/1899 6:59:0#));


Sincerely,

Chris O.

 

[John Vinson]

I have a user trying to query by the time field between two times. Her time
criteria are "Between #11:01:00 PM# And #6:59:00 AM#". This results in times
outside the time range, e.g., 9:00 PM, 8:00 AM, etc.

Here's the query at its simplest:

SELECT [Time Table].[Time of Day]
FROM [Time Table]
WHERE ((([Time Table].[Time of Day]) Between #12/30/1899 23:1:0# And
#12/30/1899 6:59:0#));

What is going wrong here? Does Access not "see" times spanning midnight as
within the same time range, e.g., from 11pm to 7am?

TIA,
Steve Vincent
(e-mail address removed)

Date/Time fields are stored as Double Float numbers, a count of days
and fractions of a day (times) since midnight, December 30, 1899. If
you don't specify a date, and instead just give a time value, then it
will be a time on that long-ago late December day. 11:01pm IS in fact
after 6:59am.

If you want to span midnight, include the date in your field;
#10/15/2006 11:01pm# is in fact before #10/16/2006 06:59am# and the
range will work perfectly well. #11:01pm# by itself might be on ANY
day; you can't assume that Access will know which day you mean.

If for some reason you cannot, then you need to break it into two
ranges: > #23:00# AND <= #23:59:59# OR < #07:00#

John W. Vinson[MVP]

 

[Guest]

John, thanks very much for the advice. I think I might have to use your
break-it-up technique, since I believe the user is storing "just" the time,
so it's going to be that same winter day.

Thanks again,
Steve Vincent
(e-mail address removed)

I have a user trying to query by the time field between two times. Her time
criteria are "Between #11:01:00 PM# And #6:59:00 AM#". This results in times
outside the time range, e.g., 9:00 PM, 8:00 AM, etc.

Here's the query at its simplest:

SELECT [Time Table].[Time of Day]
FROM [Time Table]
WHERE ((([Time Table].[Time of Day]) Between #12/30/1899 23:1:0# And
#12/30/1899 6:59:0#));

What is going wrong here? Does Access not "see" times spanning midnight as
within the same time range, e.g., from 11pm to 7am?

TIA,
Steve Vincent
(e-mail address removed)

Date/Time fields are stored as Double Float numbers, a count of days
and fractions of a day (times) since midnight, December 30, 1899. If
you don't specify a date, and instead just give a time value, then it
will be a time on that long-ago late December day. 11:01pm IS in fact
after 6:59am.

If you want to span midnight, include the date in your field;
#10/15/2006 11:01pm# is in fact before #10/16/2006 06:59am# and the
range will work perfectly well. #11:01pm# by itself might be on ANY
day; you can't assume that Access will know which day you mean.

If for some reason you cannot, then you need to break it into two
ranges: > #23:00# AND <= #23:59:59# OR < #07:00#

John W. Vinson[MVP]

 

[Chris2]

I have a user trying to query by the time field between two times. Her time
criteria are "Between #11:01:00 PM# And #6:59:00 AM#". This results in times
outside the time range, e.g., 9:00 PM, 8:00 AM, etc.

Here's the query at its simplest:

SELECT [Time Table].[Time of Day]
FROM [Time Table]
WHERE ((([Time Table].[Time of Day]) Between #12/30/1899 23:1:0# And
#12/30/1899 6:59:0#));

What is going wrong here? Does Access not "see" times spanning midnight as
within the same time range, e.g., from 11pm to 7am?

TIA,
Steve Vincent

Steve Vincent,

My first thought may be the most irrelevant, but from the above it
appears you have test dates in the 19th Century.


In any event, in this time-related case, "BETWEEN" has a start time
and an end time.

The code you have given above has an end time that is earlier than
the start time.

Start Time: #12/30/1899 23:1:0#

Is after:

End Time: #12/30/1899 6:59:0#

Normally, this should not happen. The end time should always be
later than the start time.

Perhaps the End Time should be: #12/31/1899 6:59:0#));


Sincerely,

Chris O.