Re: Can't get Access to run a query with a time field
From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 09/29/04
- Next message: Paul fpvt2: "Re: DISTINCTROW"
- Previous message: Tina: "Delete Query"
- In reply to: Ronslevy: "Re: Can't get Access to run a query with a time field"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Sep 2004 11:04:19 -0400
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
"Ronslevy" <Ronslevy@discussions.microsoft.com> wrote in message
news:2E784F31-AAC5-4F77-AE5B-84385948301A@microsoft.com...
> 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
>
> "Michel Walsh" wrote:
>
>> 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
>>
>>
>>
>> "RonSLevy" <RonSLevy@discussions.microsoft.com> wrote in message
>> news:C6D9087A-0582-4315-9E60-7AA3769A6B66@microsoft.com...
>> > "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.
>> >
>>
>>
>>
- Next message: Paul fpvt2: "Re: DISTINCTROW"
- Previous message: Tina: "Delete Query"
- In reply to: Ronslevy: "Re: Can't get Access to run a query with a time field"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
