Simplifying expressions by assigning parts to variables error

[Huddle]

I have a report with many unbound fields with expressions (over 200). I've began getting this error message. This may seem like a dumb question but what does it mean when it speaks of simplifying the expression by assigning parts to variables. I put a few examples of some of the expressions below.

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

=Count(IIf([District]=1,0))
=Count(IIf([District]=1,IIf([FSL]=4,0)))
=[D1Final4]+[D1Waiver4]+[D1PreMist4]


=[D1TotalCom4]/[D1TotalReq4]

[June7]

The IIf are missing the value if condition is false.

=Count(IIf([District]=1,0,1))

=Count(IIf([District]=1,IIf([FSL]=4,0,1),0))

[Huddle]

The formulas worked on a previous version without the false condition. What changed was a table was split and I had to add a table to the record source which I guess cause an addtional step in the process of analyzing and caused the error. I don't think the false condition makes a difference.

[June7]

Don't know why it worked. The true and false parts are required arguments.

[Huddle]

It has something to do with the subform. If I remove the subform the error does not appear. When I try to open the subform alone it gives the error. I cannot attach an example so if anyone have any suggestions on what I should try I'll post my findings once I've solved it.

[June7]

Why can't provide example or the db for analysis? Follow instructions at bottom of my post.

[Huddle]

Governmental Security Restrictions

[June7]

Don't care about real data, just the Access file structure. Dummy records could help with analysis but not essential. Otherwise, good luck.

[Huddle]

I was able to get permission to send the attachment. I think I got all the necessary structure moved. The report worked as a whole until I took the commander and inspector out of the building table and put them in their own table. After that I began getting the error and the sub report will not load.

[June7]

First off, I would not use Count, I would use Sum.

=Sum(IIf([District]=1,IIf([FSL]=4,1,0),0))

I should have seen that in examples of your first post.

However, I did experiment with the report structure. I made a copy and then copied all the controls from the subreport to the main report, deleted the subreport container and inserted a page break control. I did get error message about too many fields on report so I deleted the Command section. Report then opened without error. So I made another copy of report and subreport, deleted the Command section from the subreport. The report/subreport arrangement then opens without error. When I restored the Command section and deleted FY, the error returned. So I hope this helps you narrow down source of the error.

[Huddle]

I thought I found the field that was causing the error. I got it to work when I put in FY12 but when I put in FY11 it gave the error, which makes no sense at all.

In regards to Sum vs Count. Should I be replacing all my expressions with Sum rather than Count or just the Total fields.

[June7]

I tested for FY11 with the Command section removed and report runs.

Where was the correction you made?

I would use Sum in expressions with IIf. But there are a lot of Counts in this report. If Count actually works, don't change, I just don't think I would have used Count.

[Huddle]

By process of elimination I believe it was the Total Required / Unassigned Field. When I changed it to =Count(IIf([Command]="",0)) then the report worked without the error but only when FY12 was selected. The FY selected should not make any difference. I check the queries behind using FY11 and FY12 and they both work fine.

[Huddle]

Another thing I noticed. If you run the sub report alone you get different results then you do when you run it with the full report. I have a mess!!!!!!!!!!!!!!

[June7]

What textbox is that calculation in?

Can't disagree but should be doable.