Columns With Date/Time Subtract and Give D:HH:MM

[uneek78]

In my query I have 2 columns. In each column there is a date & time. I have the columns subtract from each other and in another column give:

D:HH:NN (which equals to DAYS:HOURS:MINUTES)

Everything is correct except the day column, it's kinda correct. No matter what everything equals out to the days column always gives a 30 + whatever the true amount of days between the 2 columns are.

Example 1:

1/1/09 8:00am(column 1) - 1/1/09 9:30am(column 2) = 30:01:30(column3)

Example 2:

1/1/09 8:00am(column 1) - 1/2/09 9:00am(column 2) = 31:01:00(column3)

Here is what I have in the Expression Builder:

Countdown: Format([Time of Findings]-[Exam Ordered],"d:hh:nn")



Once again, everything displays correctly except the day column. It always adds 30.

 

[DCrake]

You could try

Countdown: iif(DateDiff("d",[Time Of Findings],[ExamOrdered) > 0,Format([Time of Findings]-[Exam Ordered],"d:hh:nn"),Format([Time of Findings]-[Exam Ordered],"hh:nn"))

David

 

[namliam]

Try reading this thread for a start
http://www.access-programmers.co.uk/forums/showthread.php?t=182636&highlight=D:HH:NN

 

[DCrake]

Thought this question was familiar but was to lazy to search for it.
David

 

[uneek78]

I used:

Countdown: IIf(DateDiff("d",[Time of Findings],[Exam Ordered])>0,Format([Time of Findings]-[Exam Ordered],"d:hh:nn"),Format([Time of Findings]-[Exam Ordered],"hh:nn"))

It excludes DAYS all together. And if I add D(days) back into the equation I get 30 once again.

 

[raskew]

Hi -

Try playing with this:

x = datediff("n", #01/01/2009 12:30:00#, #01/02/2009 16:36:30#)

? format(x\1440, "00") & ":" & format((x mod 1440)\60, "00") & ":" & format(x mod 60, "00") 

[U]01:04:06[/U]

added:

Used as a function:

Public Function ddhhnn_diff(dtein As Date, dteout As Date) As String

're: http://www.access-programmers.co.uk/forums/showthread.php?t=183059
'*******************************************
'Purpose:   Returns DateDiff as dd:hh:nn
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? ddhhnn_diff(#01/01/2009 12:30:00#, #01/02/2009 16:36:30#))
'Output:    1) 01:04:06
'*******************************************
Dim x As Integer

   x = DateDiff("n", dtein, dteout)
   ddhhnn_diff = Format(x \ 1440, "00") & ":" & Format((x Mod 1440) \ 60, "00") & ":" & Format(x Mod 60, "00")

End Function

HTH - Bob

 

[uneek78]

Unfortunately I am not experienced in coding. I wouldn't be able to begin to know where to insert the code given.

 

[raskew]

Hi -

1) Copy/paste the function to a standard module.

2) Create your query and, in design view, in a new field, place:

ddhhnn_diff([MyStartDte],[MyEndDte])

....substituting your actual field names.

3) Run the query.

Bob