Turn Around Time Query

[mattster1010]

Afternoon All,

I have a query that shows the number of days elapsed since a record status was set from outstanding to completed using DateDiff. I have two seperate DateDiff columns in a query, one to work out the number of days and the other to calculate the number of minutes between outstanding and completed. I use =[Text19]\60 & Format([Text19] Mod 60,"\:00") to get the correct time in time format.

My next challenage though is to minus 780 off the total elapsed minutes(13 hours) if the datediff days column is equal to 1 for each record. If the total is equal to 2 then minus 1560 (26 hours) etc etc going all the way up to 10. Is this possible to do in the query builder or would I have to create some VBA on the actual report?

Regards,

Matt

[CreganTur]

You could use some IIF (not a typo) statements in your query's SQL.

Open the SQL view of a query, and then use Help and lookup "IIF" for more information.

IIF([Text19] = 1,=([Text19]\60) - 13 & Format([Text19] Mod 60,"\:00"), IIF([Text19] = 2,=([Text19]\60) - 26 & Format([Text19] Mod 60,"\:00")

The above is a very rough example that is completely untested... I just hope it gives you an idea of where to start.