Queries and separate date/time fields

[Guest]

Hello, I have 2 tables and each has separate date and time fields. I probably
should have had the date/time in one field but it's kind of late now. In any
case I need to compare the date and time of one record to the date and time
of the other record. I have a form/subform that uses data from the two
tables, the main form gets records from Table 1, the subform looks at the
date field of the main form to display the last record entered (of table 2)
prior to that date in the sub form. The problem is that depending on the last
record that was entered on table 2 it may be later than the date/time on the
main form. For Example, if the main form has a Date 2/3/06, Time 8:30, the
subform displays The record entered on 2/3/06 10:30 because it is just
looking at the date. I have been unsucessful in writing a query to look at
both the date AND time fields together and comparing them in sequence. Any
help would be appreciated.

 

[Allen Browne]

You can add a date and time field like this:
[MyDate] + [MyTime]

However, times are stored as a floating point value, so trying to match time
values is inherently unreliable. You might be better to convert the time to
a number representing "minutes-past-midnight", and comparing that.

To do that, create a query into the first table.
In the Field row, enter:
MinuteOfDay: DateDiff("n", #0:00AM#, [MyTime]
Add the * to the grid, so it outputs all the fields as well.
Save the query.

Repeat for the other table.

Create a new query that uses both queries as input "tables." In the upper
pane of query design, drag the date field from one table, and drop on the
matching date field in the other table. Access draws a join-line between the
2 tables. Repeat this for the MinuteOfDay field as well, so there are now 2
join lines between the tables.

The query will not match the 2 tables based on the combination of date and
time.