Subtracting time from time

[Guest]

I have two "Short Time" fields that are used to enter time of day in military
format (eg, 1:00 = 13:00). THe 2 times are the starting [Start] and ending
[End] times of appointments, I need to query for all appointments over 1 hour
in duration.

So I have a field in my query by design table: Duration: [End]-[Start]. THe
only way I have been able to get the query to output all records over 1 hour
in duration is to use this criteria: >#1:00:01 AM#.

WHy the AM you ask? I dont know-- Access puts that in there for me when I
try to write the criteria. ANd if I don't stick the one second on at the end,
the result includes appointments of one hour.

Sooooooo... I think what is happening is that my expression is returning a
time of day, not just a time duration. Can't I just get it to return an
elapsed time? Is there a function I can use for that? I really don't trust my
approach, although it does SEEM to work. ANy ideas?

THanks!

 

[Guest]

Try

SELECT TableName.*
FROM TableName
WHERE DateDiff("h",[StartTime],[EndTime])>1

 

[Guest]

I took your approach and added one little tweak:
DateDiff("n",[Start],[End])/60>1. This seems to work--Thanks!

When I used hours it excluded some appointments that it should have
included. (For example, an appointment from 10:00 to 11:30 it excluded, but
an appointment from 12:45 to 14:15 it correctly included. Both appointments
were an hour and a half in duration, so I can't explain it. But changing the
DateDiff to minutes and dividing by 60 seems to have done the trick.)

Try

SELECT TableName.*
FROM TableName
WHERE DateDiff("h",[StartTime],[EndTime])>1

--
\\// Live Long and Prosper \\//
BS"D

I have two "Short Time" fields that are used to enter time of day in military
format (eg, 1:00 = 13:00). THe 2 times are the starting [Start] and ending
[End] times of appointments, I need to query for all appointments over 1 hour
in duration.

So I have a field in my query by design table: Duration: [End]-[Start]. THe
only way I have been able to get the query to output all records over 1 hour
in duration is to use this criteria: >#1:00:01 AM#.

WHy the AM you ask? I dont know-- Access puts that in there for me when I
try to write the criteria. ANd if I don't stick the one second on at the end,
the result includes appointments of one hour.

Sooooooo... I think what is happening is that my expression is returning a
time of day, not just a time duration. Can't I just get it to return an
elapsed time? Is there a function I can use for that? I really don't trust my
approach, although it does SEEM to work. ANy ideas?

THanks!