Difference between Time fields? - Access 2000 Query

[mark]

I'm stuck:
In a query I am trying to compare two fields with a date/time data type and a
LongTime format to get the difference in hours (minutes and seconds if
possible). After that I need to determine if the difference is greater than 5
hours.
The part of the query looks like this so far (IIf([CT]="EQ" AND
([NDTIME]-[STTIME])>5,"Yes","No" ) AS EQChg) but doesn't come out right.
NDTIME = 10:05:02 PM
STTIME = 3:01:02 PM
Running a simple select statement subtracting the two fields with the above
times returns a value of 0.29444444444.

HERE IS THE QUERY:

SELECT Sum(AHRET) AS MonChgRtn, Count(VehicleID ) AS ChgCount, Min(STSOC) AS
MinSTSOC,
Max(NDT) AS MaxNDT, Max(STT) as MaxSTT,
IIf(MaxNDT>MaxS TT,MaxNDT,MaxST T) AS MaxTemp,
IIf([CT]="FC","Yes","No ") AS FullChg,
IIf([CT]="EQ" AND ([NDTIME]-[STTIME])>5,"Yes","No" ) AS EQChg, CT
FROM ChargeLog_longt ime
WHERE (((STDATE)=#10/6/2003#) AND ((VehicleID)=10 101010))
GROUP BY IIf([CT]="FC","Yes","No "), IIf([CT]="EQ" AND
([NDTIME]-[STTIME])>(format(5,"tt tt")),"Yes","No "), CT;

Thanks,
mark

[Larry Linson]

Take a look at the DateDiff built-in function. Get the result in seconds and
break that result down into hours, minutes, and seconds (the integer divide
\ and Mod operators will come in handy).

Larry Linson
Microsoft Access MVP

"mark" <[e-mail] ast.net> wrote in message
news:72******** ************@co mcast.com...

I'm stuck:
In a query I am trying to compare two fields with a date/time data type and a LongTime format to get the difference in hours (minutes and seconds if
possible). After that I need to determine if the difference is greater than 5 hours.
The part of the query looks like this so far (IIf([CT]="EQ" AND
([NDTIME]-[STTIME])>5,"Yes","No" ) AS EQChg) but doesn't come out right.
NDTIME = 10:05:02 PM
STTIME = 3:01:02 PM
Running a simple select statement subtracting the two fields with the above times returns a value of 0.29444444444.

HERE IS THE QUERY:

SELECT Sum(AHRET) AS MonChgRtn, Count(VehicleID ) AS ChgCount, Min(STSOC) AS MinSTSOC,
Max(NDT) AS MaxNDT, Max(STT) as MaxSTT,
IIf(MaxNDT>MaxS TT,MaxNDT,MaxST T) AS MaxTemp,
IIf([CT]="FC","Yes","No ") AS FullChg,
IIf([CT]="EQ" AND ([NDTIME]-[STTIME])>5,"Yes","No" ) AS EQChg, CT
FROM ChargeLog_longt ime
WHERE (((STDATE)=#10/6/2003#) AND ((VehicleID)=10 101010))
GROUP BY IIf([CT]="FC","Yes","No "), IIf([CT]="EQ" AND
([NDTIME]-[STTIME])>(format(5,"tt tt")),"Yes","No "), CT;

Thanks,
mark