Report formula issue (Sum(iif(...)))

I though the limit was 11 or so.
Try this:
=Sum([field1] = "Y")*-1
for each.
Are you trying to sum all of the 'Y" answers in fields 1-10?

jerryb30,

I think you're thinking of...

Number of fields or expressions you can sort or group on in a report: 10

Not quite the same thing :)

Regards,

Patrick

I was actually thinking nested iif statements.......

The new formula didn't work either.  It's very strange.  When I click on the preview, it flashes and then goes back to the design view.  I take away any two of them and it works no problem.

I actually think it is too intense for the db to handle.  I created a new db and imported everything without luck.  As I page through with 8 formulas, when I get to the page that would have the results for the formulas it crashes the db.

I take away half of them and it doesn't crash.

It's only 15 columns, 10 of which have summary formulas; and 250 records collected using a pass through query from oracle.

Jerry,

> I was actually thinking nested iif statements.......

Yeah, that's about right :)

Patrick

Most likely this is a "one-off" problem.  There is something in the db/report that is overwhelming it.  I will just deal and not do a summary.  It's either all or non.  I can't just do some of them:-)

Thanks to all for the effort.

Try doing your calculation in the query instead of in the report, and you won't have a problem.

If the field is a Y/N field...

I assume that you defined the field as a boolean...  which means that each Y = -1

Sum the field and multiply by -1 or do a Abs(Sum(field...

I think if you explained exactly what you are trying to accomplish (in english... not code) it would make finding the solution easier

Rich

I implied but did not explicitly ask...  Why are you using an IIF at all?

Seems like a waste of time, CPU cycles and logic

Rich

If the field is not a boolean... and can't be converted to one...

In the detail section create a hidden field for each of these fields with recordSource as

=(Field# = "Y")

This will give you your boolean value of -1 and 0

You can simply create a field to sum each of these hidden fields to do your work and eliminate the IIF

Rich

There must still be an issue with the report, because I changed the query to give me 1's and 0's and them put all 10 text boxes in the footer with =sum(field1), ... =sum(field10) and it still flashes and comes back to the design view.  I delete any two and scroll through the pages and when I get to the page with the summary, the db crashes.

I give...

First procedure did not work.  Question:  should all db's be opened this way or is this only used in certain circumstances?  New to me.

I'll have to get to the second procedure, next week.

The Decompile should be done WHEN NEEDED

I do it as a matter of publishing an update for my database to the users or when "THINGS" happen

When STUFF doen't work as expected and everything seems to be correct...

Access holds onto alot of CR*P as you make changes
Edit a form and your not editing the original form...  you are editing a Copy of the original form that has been created at the end of the file.  Save it and the original is marked as unused...  but it is still there.

Compacting cleans up the garbage... but sometimes a compiled version of your code is saved (it happens automaticly when you run your code or query the first time or you can do it manually)   When you edit code, Access is supposed to mark the "Compiled" version of your object as out of date (though it can not automaticly reclaim the space) but sometimes (especially when you have a large app) the table that access maintains on the compiled state... does not get updated.  Access sees that the object is still compiled (though you have made changes) and uses the compiled code.

Now I don't know if that has anything to do with your problem...  but by decompiling...  we eliminate this as a source of the problem.


Rich

I usually run compact and repair before publishing.  The orig file was 2.3mb, but after decompiling and then compact and repair it went to 1.9mb.  It'll save everyone time, just that part - 400k file size difference.  Thanks for that tidbit.

Where's the threshold of file size that starts to degrade an access application?  This db table source is in Oracle, so this is just forms and reports along with the pt queries.  A lot of reports and pass thru query code.

Upped the points.

Did you get it figured out?

It was one of those things that I spent way to many hours on for what it was worth and had to move onto other things.  In a normal situation any of the suggestions would have fixed the issue.  They were all very good from a perspective of not being able to see the issue first hand.  Thank you all for attempting.

I'm sure rebuilding from scratch will fix it.  If not, I'm not supposed to do it:-)  I have not other issues with any other reports within the db.