Case Statement and Invalid type for function call If error

I'm trying to write a case statement that calculates the number of dates between the submit date and the receipt date.  If the receipt date is equal to 9/9/9999(dummy date I added to the data) then I want to give today's date.  If there is a receipt date then I simply want to subtract the two dates.

This works

case

WHEN [Date Received] = Date(9999,9,9) THEN DateTimeNow()

ELSE

[Date Submitted]

END



This works

DateDiff("dd",[Date Submitted],[Date Received])



But when I try combining them in this CASE statement, I get the Invalid type for function call 'If' error.

case

WHEN [Date Received] = Date(9999,9,9) THEN DateTimeNow()

ELSE

(DateDiff("dd",[Date Submitted],[Date Received]))

END



Rewritting the case statement to this, gets rid of the ERROR but doesn't calculate the correct answer.  It give a date of "1/1/0001" instead of giving a number

case

WHEN [Date Received] = Date(9999,9,9) THEN DateTimeNow()

ELSE

Date(DateDiff("dd",[Date Submitted],[Date Received]))

END

How do I get rid of the Invalid type error?  

(3) Answers

Login