Adding to an Iif and a DateDiff

[kupe]

This little wonder in the query does the job well.

OOW: IIf(IsNull([LastJobEndDate]), DateDiff("d",[DoA],Date()), DateDiff("d",[LastJobEndDate],Date()))


But if the field "RefusedWork" is marked “Yes", then the date to make the calculation is from the field, "FirstRefusalDate".

Woe is me. The brain doesn't know where nor how to throw this into the calculation. Can anyone help please?

 

[KenHigg]

It can be done but I would put it in a function...

???
ken

 

[kupe]

Um ... like ...? (I mean just roughly, if you prefer)

 

[KenHigg]

pseudo:

function:

myOOW(rw as string, lastDate as date, firstReflDate as date, DoA as date) as date

if rw = false then
If IsNull([LastJobEndDate]) then
myOOw = DateDiff("d",[DoA],Date())
else
myOOW = DateDiff("d",[lastDate],Date()))
end if
else
If IsNull([LastJobEndDate]) then
myOOw = DateDiff("d",[DoA],Date())
else
myOOW = DateDiff("d",[firstRefDate],Date()))
end if
end if

end function


-----------------
I did this really fast - it may not be correct but maybe you get the idea...
kh

 

[kupe]

Really good of you to go to that trouble, Ken. Thanks very much. Cheers

 

[Mile-O]

Public Function GetOOW(booRefused As Boolean, varLast As Variant, _
    varFirstRef As Variant, dteDOA As Date) As Date

    If booRefused Then
        If IsDate(varFirstRef) Then
            GetOOW = DateDiff("d", CDate(varFirstRef), Date)
        Else
            GetOOW = DateDiff("d", dteDOA, Date)
        End If
    Else
        If IsDate(varLast) Then
            GetOOW = DateDiff("d", CDate(varLast), Date)
        Else
            GetOOW = DateDiff("d", dteDOA, Date)
        End If
    End If

End Function

 

[kupe]

That looks very good, SJ. Many thanks for working that out. Most grateful