[SOLVED] I'm getting an Error with my iff statement

I'm getting an Error with my iff statement

I have a query for a report. One of the fields in the report I need to count the number of cases carried forward to the next year because they were not closed the previous year.

My query is made up of three tables. The column I get #error I have the following written

Carryfoward:Iff([dateReceived]<[forms]![Form_Report]![textStartDate]) AND (IsNull([DispositionDate])),1,0)

The field DispositionDate is in Table_Disposition

Carryfoward:Iff([dateReceived]<[forms]![Form_Report]![textStartDate]) AND (IsNull([DispositionDate])),1,0)

I expected to get a number. When I run the query from the form, I get a popup that wants me to Enter Parameter Value for CarryForward. When I run the query from the query, I get #error in the CarryForward colunm.

Solution

Try using IIf:

Carryfoward: IIf([dateReceived]<[forms]![Form_Report]![textStartDate] AND IsNull([DispositionDate]),1,0)

or alternatively:

Carryfoward: Abs([dateReceived]<[forms]![Form_Report]![textStartDate] AND [DispositionDate] Is Null)