IIFStatement #Name

[MNNovice]

My report has a text box called txtFedAmount. Here I am trying to get the total amount of money for federal revenue.

FieldName      DataType        Description
RevDescr         text              Federal / Local etc
Budget            Currency        dollar amount

I am trying to add the federal amount of revenue portion in txtFedAmount

=IIf(([RevDescr]) Like “Federal”,Sum([Budget]),"")

But I keep getting #Name? What am I doing wrong?

[ChipR]

"Like" is used in SQL; it's not a valid operator in an IIf statement.

[MNNovice]

ChipR:

Thanks. I found a solution to this problem. In the event some other novice like myself gets into this rut, here is the solution:

=Sum(Abs([RevDescr]="Federal") * [Budget])

[NeoPa]

A bit late to the show here but let me see if I can clear up some misconceptions.

IIf() is a function that can be used from VBA OR SQL. Like is not usable within VBA code, however it is fine within SQL. The formula for a control would be SQL based, therefore Like is fine (in this case).

Abs() returns the absolute (unsigned) value of the passed parameter. The Boolean value TRUE is numerically equivalent to -1, and FALSE to 0, so this will work when the value is exactly equal to "Federal". This is also true for your original usage of Like. Like is misused here. = was the comparator required in these circumstances.

Lastly, to the actual, original problem :

“Federal” <> "Federal"

Notice the quotes. The one on the left is not using quote characters that are even recognised by Access as such. Hence the error reported.

A more conventional solution (simply fixing the original code) might be :

=Sum(IIf([RevDescr]='Federal',[Budget],0)

I think I prefer your code though.