Time difference

[Guest]

I need to modify this statement:
(DateDiff("n",[QH_SUBMIT_TS],[QH_RELEASE_TS])/60)-Minus_Non_Work_Time([QH_SUBMIT_TS],[QH_RELEASE_TS])
The Minus_Non_Work_Time function substracts 16 hours everytime, but I need
to add back an additional hour for each hour QH_SUBMIT_TS is greater than
5pm. I know I need some form of IF statement, but I can't figure it out.
For example, if the QH_SUBMIT_TS is 6:30 PM, then I need to add 1 hour. If
the QH_SUBMIT_TS is 7:30, then I need to add 2 hours back. Please help.

 

[Jim Bunton]

I need to modify this statement:
(DateDiff("n",[QH_SUBMIT_TS],[QH_RELEASE_TS])/60)-Minus_Non_Work_Time([QH_SUBMIT_TS],[QH_RELEASE_TS])
The Minus_Non_Work_Time function substracts 16 hours everytime, but I need
to add back an additional hour for each hour QH_SUBMIT_TS is greater than
5pm. I know I need some form of IF statement, but I can't figure it out.
For example, if the QH_SUBMIT_TS is 6:30 PM, then I need to add 1 hour.
If
the QH_SUBMIT_TS is 7:30, then I need to add 2 hours back. Please help.

How about:

HoursYouWant = Non_Work_Time function ()
HoursToAdd = DateDiff("h",QH_SUBMIT_TS, Now())
If HoursToAdd > 0 then
HoursYouWant = DateAdd("h",HoursToAdd,HoursYouWant)
End If

you can write this as a function and use it in a select statement with
appropriate parameters

OR you might put all this in an IIF statement
SELECT IIF(DateDiff("h",QH_SUBMIT_TS, Now())>0, DateAdd("h",HoursToAdd,
HoursYouWant), HoursYouWant) AS NewColumnName


[substituting your original datediff statement for HoursYouWant]
[IIF Condition, DoThisIfTrue, DoThisIfFalse)

DoThisIf* may be another IIF statement if you want

Note: what you have called a statement