iif statement overtime shift dif

[tubar]

The user inputted… IN:4am OUT:8am

basically hours worked between midnight and 5:59am are paid at a different scale depending on position. So when a person works 4am – 8am 2 of those hours need to be paid at the special rate and 2 hours at regular.

The following iif statement in my query returns 24…when it should return 2

otadd: IIf([in] Between #12:00:00 AM# And #5:59:00 AM#,(DateDiff("h",[in] Between #5:59:00 AM# And #12:00:00 AM#,0)-DateDiff("h",[in] Between #6:00:00 AM# And #11:59:00 PM#,0)))

any suggestions on changing my iif statement?

 

[pr2-eugin]

Most certainly, your DateDiff function is wrong. look at the foolowing link on DateDiff function and its usage..

http://www.techonthenet.com/access/functions/date/datediff.php

 

[tubar]

I am new to this so bare with me…I have a general date column entitled “out”. I am building an iif statement that would return just the hours between midnight and 6am.

otadd2: IIf([out]>#23:59:00#<#05:59:00#,DatePart("h",[out],0))

this statement returns all hours after midnight..can some one help or point me in the right direction?