Time and "Between"

[croy]

When using "Between [Start] and [Finish]" where Start and
Finish are Time fields, I've noticed some inconsistencies
where one or both of the times are the same as one or more
of the reference times.

The criteria for the reference time is

Between [Start] and [Finish]

All of the reference times are right on the hour.

When I query for matches to a reference time of 09:00, and
there start times of 09:00, some of them get picked up, but
not all.

So, how does Access determine whether or not to include
values that are right on the line?

Better yet, how can I get solid results from this query?

 

[Michel Walsh]

x Between y AND z

assuming z >= y, is the same as

x >= y AND x <= z

If z < y, the evaluation automatically reverse the comparison for you:


? eval ( " 5 BETWEEN 7 AND 2 " )
-1

rather than returning false.


When you use floating point, there is always the possibility of rounding
error. What is displayed as 8:00:00 can be, in fact, 7:59:59.999999
and thus, be excluded from the interval, but most of the time, the problem
occurs because people use 'date without time' :

BETWEEN #1/1/1# AND #2/2/2#


which will not take #2/2/2 18:00:00#

since it occurs exactly 18 hours after the specified upper limit.


Vanderghast, Access MVP

 

[Lord Kelvan]

time is stored in a double format 00:00:00 hours minutes and seconds
you may have time in there that incluedes seconds outside the time
range you could always

so you could format your field then run the between statement as the
criteria on that field

format([timefield],"h:nn")

will remove any leading 0's

and

format([timefield],"short time")

will have leading 0's

then your between statement can be as it was and will work on the new
format remember if you use the non leading 0 format you cannot type in
09:00 you have to type in 9:00

hope this helps

Regards
Kelvan

 

[Bob Barrows [MVP]]

time is stored in a double format 00:00:00 hours minutes and seconds

No it isn't. Time is stored in the decimal portion of a Double number.
..0 = midnight, .5 = noon. No format!

 

[Lord Kelvan]

yea i know i am sorry i miss plased the words it is too early in the
morning it should be

in a double in the format 00:00:00

when you format time this is the format it is in simmilar how dates
are in dd/mm/yyyy or mm/dd/yyyy or for the crazy people yyyy/mm/dd or
100 other formats you can imagine

 

[Bob Barrows [MVP]]

yea i know i am sorry i miss plased the words it is too early in the
morning it should be

in a double in the format 00:00:00

Sorry, but that's still an incorrect statement.
Strings have format.
Numbers have no format.
Date/Times are stored as numbers of type Double.
Therefore date/times are stored without format.

A format is applied by the interface when the value is converted to a
string for display (that is what the format() function does: it converts
a value to a string so a format can be applied to it).

 

[Lord Kelvan]

well yea i know but i tend to explain it in a simple method for people
to understand and because that is what they see it is eaiser for them
to understand. i have come up as a tutor and so i tend to over
simplfy when exaplining things

 

[croy]

x Between y AND z

assuming z >= y, is the same as

x >= y AND x <= z

If z < y, the evaluation automatically reverse the comparison for you:


? eval ( " 5 BETWEEN 7 AND 2 " )
-1

rather than returning false.


When you use floating point, there is always the possibility of rounding
error. What is displayed as 8:00:00 can be, in fact, 7:59:59.999999
and thus, be excluded from the interval, but most of the time, the problem
occurs because people use 'date without time' :

BETWEEN #1/1/1# AND #2/2/2#


which will not take #2/2/2 18:00:00#

since it occurs exactly 18 hours after the specified upper limit.


Vanderghast, Access MVP

When using "Between [Start] and [Finish]" where Start and
Finish are Time fields, I've noticed some inconsistencies
where one or both of the times are the same as one or more
of the reference times.

The criteria for the reference time is

Between [Start] and [Finish]

All of the reference times are right on the hour.

When I query for matches to a reference time of 09:00, and
there start times of 09:00, some of them get picked up, but
not all.

So, how does Access determine whether or not to include
values that are right on the line?

Better yet, how can I get solid results from this query?

You nailed it. Thank you!