Access date comparisons and joins

[KHerm]

I have a query that uses another query and a table that need to join on a date. The date must use the date/time value for the results to be accurate. The result set of the first query does show date/time in the result. However the second query never finds a join from the first query and the table because (I'm presuming) the join in the second query is NOT using the time part of the field. I &quot;proved&quot; this when I did another query using >= and < than the date range and I got the proper result set.

Using DateValue(date) doesn't work because I lose the time stamp. Will I have to query the hour and minutes using Hour(date) and Minute(date) to create a proper join? Or is there another way to join a query result column with a table column that will not lose the time stamp?

Sure hope I explained that well enough!

Ken

 

[RickSpr]

The join should automatically use the full value of each field--both date and time. It sounds as if your time parts don't actually match. In the worst case, it might be that they're different by less than one second, so even if they look exactly alike when formatted, they're really slightly different.

Try using CDbl(field) to see the actual values of the fields and see if the fractions match.

Since date/time fields are stored as floating point numbers, they're subject to the same warnings about exact matches as Singles and Doubles are. You might have to do a range comparison, or round one or both values to the nearest second, to get a usable comparison. Rick Sprague

 

[KHerm]

Thank you, Rick, CDbl(field) found the problem. The first query does pull from a different table than the second query. In each table the date, hour, minute and second match until I compare with CDbl.

We discussed it, and think we're OK comparing down to the minute so now we compare with:

DateValue(date)&Hour(date)&Minute(date)

and it seems to be right.

Thanks for the mystery solver.

Ken