DateDiff error in table calculated field

Question

I'm getting the error "Invalid Sytax - You may have entered a comma without a preceding value or identifier"

Table Calculated field Expression - IIf(And([BR_Date_reported]<>"",[BR_Date_deleted]<>""),DateDiff("d",[BR_Date_reported],[BR_Date_deleted]),"")

I cannot find a way to make it work. any suggestions appreciated.

Answer 1

The Calculated Data type is somewhat limited in the expressions it can use. I wrote a blog on that.

Bottom line is I don't use the Calculated data type. If I want to do calculations, I do it in queries or controls on forms.

Answer 2

Sorry, I didn't realize this was a calculated field in a table design. Like Scott, I don't use them as I prefer to create the value in a query or control source.

Answer 3

getting response "The expression cannot be used in a calculated column"

Answer 4

I believe you are using Excel syntax. Try:

IIf(Not IsNull([BR_Date_reported]) and Not IsNull([BR_Date_deleted]),DateDiff("d",[BR_Date_reported],[BR_Date_deleted]),Null)