relationships between time fields in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to relate 2 tabels in a query on a time field. Both fields are
formatted to Short Time. Some relate Ok and others not. What can I try?
 
Hi

The format only affects how the data is displayed - not the data itself nor
does it restrict data entry in the way you might expect.

E.g. A date/time field formatted to short time (hh:mm on my system) won't
allow me to enter "Hello" or 7 but will allow all of the following...

08:01
01/01/2007 08:01
08:01:59

And will display them all as 08:01

but none are equal and so won't "relate"

If you are happy that any "stray" date parts or seconds are simply not
needed then run the following SQL... (backup first?)

update mytable set mytime = format(mytime,"hh:mm")

To prevent further invalid entry consider the use of input masks or run code
to check the entry before saving it - if you need to warn the user or use the
above format statement to automatically drop the dates and seconds without
telling the user.

Hope this helps

Andy Hull
 
Another option would be to strip off the time in the query:

WHERE CLng([OneField]) = CLng([OtherField])
 
I am trying to relate 2 tabels in a query on a time field. Both fields are
formatted to Short Time. Some relate Ok and others not. What can I try?
Click to expand...

A Date/Time value is stored as a Double Float count of days and fractions of a
day (times) since midnight, December 30, 1899.

The problem with a Double is roundoff error. If you're just storing a time,
it's storing it to about 14 decimals precision - under a nanosecond precision.
However you can only display the time to the nearest second! Therefore two
records may both *show* 11:35:15 but actually be different, so the join won't
work. The format is irrelevant and actually makes things worse (if you're
formatting to the nearest minute, then two times that differ by 58 seconds
will look the same but actually be different).

I've tried to avoid joining on date/time values because of these problems.


John W. Vinson [MVP]
 
Back
Top