Report Query

[pmcmicha]

I have a report in which it queries the database for information and runs IIf statement. This works without a problem, but when I go to create a new report and then try this same query, I get an #Error for the result. I tried a copy and paste of the statement, no luck. Looked up the #Error and tried all relevant troubleshooting steps like making sure the field size was large enough and what not, still no dice.

=sum(IIf([TABLE].[COL1]=No,0,IIf([TABLE].[COL2]=Yes,0,IIf([TABLE].[COL3]=Yes,0,1))))

Any help would be appreciated, thanks.

 

[dhookom]

Where did you enter that expression? Is it the control source of a text box in a report section? If so, which section and what is the name of the control. The control name can't be the name of a field and the expression won't work in a PAGE header or PAGE footer.

What are the data types of COL1, COL2, and COL3? If they are yes/no, it is more appropriate to use

=sum(IIf([TABLE].[COL1]=0,0,IIf([TABLE].[COL2]=0,0,IIf([TABLE].[COL3]<>0,0,1))))

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Do yourself a favor and avoid ever using nested iifs. Nothing good can come of it. Also your logic does not make any sense unless I am missing something.

The only thing that matters is this "

.[COL3]" because no matter the value of [COL1] and [COL2] you do nothing. The only possible thing I can imagine is that you think that nested iifs short circuit, but they do not. Another reason to never use them. So your expression should simply be

=sum(IIf(

.[COL3],0,1))