Datediff() return minutes with leading zeros

[CrazyKillerMan]

I searched through the forum, and found alot of examples on DateDiff(), but I need to take two dates (both stored in a table as [Off Time] and [On Time] and subtract them. Then show them in hh:mm:ss format, keeping in mind this is in a query. The datediff() function gives me and error if I try to do this. So I tried using math to do
hours & ":" & minutes & ":" & seconds
but the minutes will not show a leading zero, same with seconds.

ie: 2:3:02 is not acceptable.

Is there any way around this?
Or am I going about this all wrong.

 

[pdx_man]

First, the DateDiff function should not be giving you an error. My guess is it is coded incorrectly, or your fields are not Date/Time.

MyMinutes: DateDiff('n',[Off Time],[On Time])

Check your quotes, you may have to alternate between using single and double quotes.

If this still won't work, check out the Format function in Access Help. Look up User-Defined Date/Time Formats (Format Function) under the Format Function.

 

[CrazyKillerMan]

Ill try changing the quotes around, and the format is correct. I get a correct answers when using 'n' but not when I'm using 'nn' (which was obtained from the User-Defined Date/Time Formats section of the help file).

 

[CrazyKillerMan]

Ok - I tried changing the quotes around and it didnt help. I moved some things around and got it to 'work' but when I say work, I mean that it says its to complicated to do a Sum(DateDiff("hh:nn:ss", field1, field2))
So....I just did a sum(datediff()) that returns seconds then i did this:

SELECT tbl_equipment.Equipment, Round(Sum(DateDiff("s",[tbl_log].[Off Time],[tbl_log].[On Time])) / 3600, 0) AS Hours,
Round(Sum(DateDiff("s",[tbl_log].[Off Time],[tbl_log].[On Time])) - Round(Sum(DateDiff("s",[tbl_log].[Off Time],[tbl_log].[On Time])) / 3600, 0)*3600 / 60, 0) AS Minutes,
Sum(DateDiff("s",[tbl_log].[Off Time],[tbl_log].[On Time]) - (Round(Sum(DateDiff("s",[tbl_log].[Off Time],[tbl_log].[On Time])) / 3600, 0))*3600 - (Round(Sum(DateDiff("s",[tbl_log].[Off Time],[tbl_log].[On Time])) - Round(Sum(DateDiff("s",[tbl_log].[Off Time],[tbl_log].[On Time])) / 3600, 0), 0))*60) AS Seconds FROM tbl_equipment INNER JOIN tbl_log ON tbl_equipment.Equipment = tbl_log.Equipment
WHERE (((tbl_equipment.Equipment)=[Forms]![frm_equip_outtage]![combo_equipment]))
GROUP BY tbl_equipment.Equipment;

but it locks up access somethin' fierce. Can anyone spot anything?

 

[CrazyKillerMan]

Ok - I optimized a little:

DateDiff('h',Sum([tbl_log].[Off Time]),Sum([tbl_log].[On Time])) AS Hours,DateDiff('n',Sum([tbl_log].[Off Time]),Sum([tbl_log].[On Time])) AS Minutes,DateDiff('s',Sum([tbl_log].[Off Time]),Sum([tbl_log].[On Time])) - DateDiff('n',Sum([tbl_log].[Off Time]),Sum([tbl_log].[On Time])) * 60 - DateDiff('h',Sum([tbl_log].[Off Time]),Sum([tbl_log].[On Time])) * 3600 AS Seconds

Now it gives me correct values, but I want to have it in hh:mm:ss format. The datediff() function wont allow me to have leading zeros

ie: DateDiff("hh:nn:ss", Sum([tbl_log].[Off Time]) - Sum([tbl_log].[On Time])) will not work. It works fine when I use:
DateDiff("h", Sum([tbl_log].[Off Time]) - Sum([tbl_log].[On Time]))
but then this only returns hours.

5:03:02 which is 5 hrs, 3 mins, 2 seconds - look like: 5:3:2

I am desperate here guys!!!

 

[CrazyKillerMan]

Well - I just formatted each type and smashed em together.

 

[pdx_man]

You are not using the Format statement ... Did you check that out as I listed above?

 

[CrazyKillerMan]

Yea, I got that - but the initial SQL was to cumbersome for a format(). But - after I got fed up and started from scratch again, I was able to make it more workable with the SQL.


Thanks for the help