By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 913 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Difference between Time fields? - Access 2000 Query

P: n/a
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>MaxSTT,MaxNDT,MaxSTT) AS MaxTemp,
IIf([CT]="FC","Yes","No") AS FullChg,
IIf([CT]="EQ" AND ([NDTIME]-[STTIME])>5,"Yes","No") AS EQChg, CT
FROM ChargeLog_longtime
WHERE (((STDATE)=#10/6/2003#) AND ((VehicleID)=10101010))
GROUP BY IIf([CT]="FC","Yes","No"), IIf([CT]="EQ" AND
([NDTIME]-[STTIME])>(format(5,"tttt")),"Yes","No"), CT;

Thanks,
mark

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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" <ms@nospam.comcast.net> wrote in message
news:72********************@comcast.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>MaxSTT,MaxNDT,MaxSTT) AS MaxTemp,
IIf([CT]="FC","Yes","No") AS FullChg,
IIf([CT]="EQ" AND ([NDTIME]-[STTIME])>5,"Yes","No") AS EQChg, CT
FROM ChargeLog_longtime
WHERE (((STDATE)=#10/6/2003#) AND ((VehicleID)=10101010))
GROUP BY IIf([CT]="FC","Yes","No"), IIf([CT]="EQ" AND
([NDTIME]-[STTIME])>(format(5,"tttt")),"Yes","No"), CT;

Thanks,
mark

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.