Can't get Access to run a query with a time field

[Guest]

"I hope you can help (at least with the Access part of this question)

I have written a software app in which I have a vb6 frontend to an Access
2002 database. The program creates a recordset based on a query that uses a
time field. In Access, the field is define as a "Date/Time" with format of
"Long Date". When it runs it doesn't find the record (which I know exists).

Even if I create a query in Acess in which one of the fields is time, when I
enter a time (e.g. 8:10:00) in the Criteria line and move to the next field,
Access converts that to #08:10:00 AM#". Now when I run the query, it doesn't
find the record and if I look at the SQL statement the query editor creates
it looks like:

SELECT [Data].[Medical Record #], [Data].Date, [Data].Time, [Data].Site
FROM [Data]
WHERE ((([Data].Time)=#12/30/1899 8:10:0#));

The time field is user inputed so it is not a matter of Access not finding
the exact time to the hundredths sec.

Even if I then change the SQL statement manually at this time to

WHERE ((([Data].Time)=#8:10:00 AM#));

it still doesn't find the record. I am confused...Is there a way to create a
query in Access that will work with a time field.

Thanks for any help.

 

[Michel Walsh]

Hi,


Two things. If you have a date, in addition to the time, you have to
supply the date too. Second, a date time value is a floating point value, so
it is preferable to test for a range than for an exact value (unless it is
an exact one, in base 2, like 0, or 0.5, or 0.25, 0.75, and so on).

WHERE (data.time-int(data.time) ) BETWEEN #8:09:30 AM# AND #8:10:30 AM#

or


WHERE (data.time-int(data.time) ) BETWEEN #8:09:59 AM# AND #8:10:01 AM#


(also note that both limits are inclusive, with BETWEEN; if that does not
fit your pattern, since a record can technically be AT the limit value and
thus be part of two "groups", use x >= b AND x < c , as example, rather
than x between b and c).




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

So even if the user enters the time from a text field as 8:10:00 it still
goes in differently? I have the date and time fields separate so that
shouldn't be a problem. Ideally I don't want to have to crete a query with a
between because there is always the possibility that I'll capture 2 records
that way. Since the user enters an exact value, why isn't it saved to the
database that way? Does it have to do with the conversion of 8:10:00 to a
floating point?

Ron

Hi,


Two things. If you have a date, in addition to the time, you have to
supply the date too. Second, a date time value is a floating point value, so
it is preferable to test for a range than for an exact value (unless it is
an exact one, in base 2, like 0, or 0.5, or 0.25, 0.75, and so on).

WHERE (data.time-int(data.time) ) BETWEEN #8:09:30 AM# AND #8:10:30 AM#

or


WHERE (data.time-int(data.time) ) BETWEEN #8:09:59 AM# AND #8:10:01 AM#


(also note that both limits are inclusive, with BETWEEN; if that does not
fit your pattern, since a record can technically be AT the limit value and
thus be part of two "groups", use x >= b AND x < c , as example, rather
than x between b and c).




Hoping it may help,
Vanderghast, Access MVP

"I hope you can help (at least with the Access part of this question)

I have written a software app in which I have a vb6 frontend to an Access
2002 database. The program creates a recordset based on a query that uses
a
time field. In Access, the field is define as a "Date/Time" with format of
"Long Date". When it runs it doesn't find the record (which I know
exists).

Even if I create a query in Acess in which one of the fields is time, when
I
enter a time (e.g. 8:10:00) in the Criteria line and move to the next
field,
Access converts that to #08:10:00 AM#". Now when I run the query, it
doesn't
find the record and if I look at the SQL statement the query editor
creates
it looks like:

SELECT [Data].[Medical Record #], [Data].Date, [Data].Time, [Data].Site
FROM [Data]
WHERE ((([Data].Time)=#12/30/1899 8:10:0#));

The time field is user inputed so it is not a matter of Access not finding
the exact time to the hundredths sec.

Even if I then change the SQL statement manually at this time to

WHERE ((([Data].Time)=#8:10:00 AM#));

it still doesn't find the record. I am confused...Is there a way to create
a
query in Access that will work with a time field.

Thanks for any help.

 

[Michel Walsh]

Hi,


Indeed. The time part is the "decimal" part. Today, at noon, is 38259.5.
That float is represented, exactly, under a IEEE format, but not all time
values are. Since a second is a little bit more than 1E-5 day, testing
against a value with an absolute difference less that 1E-5 sounds acceptable
(Jet does not allow decimal of a second, so the smallest 'quantum' you can
enter is a second, or 1/10 000 of a day), or even half of it should be more
than reasonable, to allow for 'natural' rounding error.


WHERE ABS(fieldName - desiredDateTime) <= 0.5E-5



It is always preferable to have date and time in the same field, else, the
comparisons become complex...


datePart > wantededDate OR ( datePart=wantedDate AND
TimePart >= WantedTime )


rather than just

dateTimePart >= wantedDateTime


And I don't speak of indexing, which is more effective when there is no
duplicated value (highly probable with dateTime in one field), than with
date and time in separate fields.


No, even before speed of execution, my first concern would be that the first
formulation easily makes an SQL statement very hard to read and prone to
error when you come back to it, to modify it.


But in the end, that is your time, not mine so do as it pleases
to you.



There is also the problem of the date part. Are your users with a non-US
setting? and if so, are you using default date_to_string conversion as in:

str= ".... #" & controlWithDateAndTimeSUppliedByTheUser & "# ...
"

If so, that IS the error. Try:

str = "... " & FORMAT( suppliedDate, "\#mm-dd-yyyy\#") & " ... "





Hoping it may help,
Vanderghast, Access MVP

So even if the user enters the time from a text field as 8:10:00 it still
goes in differently? I have the date and time fields separate so that
shouldn't be a problem. Ideally I don't want to have to crete a query with
a
between because there is always the possibility that I'll capture 2
records
that way. Since the user enters an exact value, why isn't it saved to the
database that way? Does it have to do with the conversion of 8:10:00 to a
floating point?

Ron

Hi,


Two things. If you have a date, in addition to the time, you have to
supply the date too. Second, a date time value is a floating point value,
so
it is preferable to test for a range than for an exact value (unless it
is
an exact one, in base 2, like 0, or 0.5, or 0.25, 0.75, and so on).

WHERE (data.time-int(data.time) ) BETWEEN #8:09:30 AM# AND #8:10:30
AM#

or


WHERE (data.time-int(data.time) ) BETWEEN #8:09:59 AM# AND #8:10:01
AM#


(also note that both limits are inclusive, with BETWEEN; if that does not
fit your pattern, since a record can technically be AT the limit value
and
thus be part of two "groups", use x >= b AND x < c , as example,
rather
than x between b and c).




Hoping it may help,
Vanderghast, Access MVP

"I hope you can help (at least with the Access part of this question)

I have written a software app in which I have a vb6 frontend to an
Access
2002 database. The program creates a recordset based on a query that
uses
a
time field. In Access, the field is define as a "Date/Time" with format
of
"Long Date". When it runs it doesn't find the record (which I know
exists).

Even if I create a query in Acess in which one of the fields is time,
when
I
enter a time (e.g. 8:10:00) in the Criteria line and move to the next
field,
Access converts that to #08:10:00 AM#". Now when I run the query, it
doesn't
find the record and if I look at the SQL statement the query editor
creates
it looks like:

SELECT [Data].[Medical Record #], [Data].Date, [Data].Time, [Data].Site
FROM [Data]
WHERE ((([Data].Time)=#12/30/1899 8:10:0#));

The time field is user inputed so it is not a matter of Access not
finding
the exact time to the hundredths sec.

Even if I then change the SQL statement manually at this time to

WHERE ((([Data].Time)=#8:10:00 AM#));

it still doesn't find the record. I am confused...Is there a way to
create
a
query in Access that will work with a time field.

Thanks for any help.