I've built a summary report that will count the number of records if certain fields contain a range of values (ex: vales = 0 through 7). I have the following code in the control source of one txt box within the report:

=Sum(IIf([cboBOQuestion1] Between "0" And "7" Or
[cboBOQuestion2] Between "0" And "7" Or
[cboBOQuestion3] Between "0" And "7" Or
[cboBOQuestion4] Between "0" And "7" Or
[cboBOQuestion5] Between "0" And "7" Or
[cboBOQuestion6] Between "0" And "7" Or
[cboBOQuestion7] Between "0" And "7" Or
[cboBOQuestion8] Between "0" And "7",1,0))

This seems to be too much for Access and will shut down on me. If I compare only 7 fields, it will run properly, but if I compare 8 fields, I run into trouble. Is there a way to simplify this code so that I can count the number of records where all 8 field values contain a value between 0 and 7?

Whatever section of the report this is in, try putting the code in the section's on format event and counting that way. I have used ADO recordsets to do large summing operations in report headers. Brute force, but works well enough if you can't find a solution.

Alternatively, can you do this counting in the query for the summary data?




mmcdonal

Look it up at: http://wrox.books24x7.com

One way to deal with excessively long expressions is to break them up by creating extra fields on the report that you can make invisible and place pieces of the expression in each one. Then you can use a simpler expression in your displayed field to just combine the values in these extra fields.

-Phil-

Hmmm... it seems that you just care if ANY of the eight are between "0" and "7". So if Question1 fits that criteria, why evaluate the rest? Try this construct and see if it works:

=Sum(IIf([cboBOQuestion1] Between "0" And "7", 1, IIf([cboBOQuestion2] Between "0" And "7", 1, IIf([cboBOQuestion3] Between "0" And "7", 1, IIf([cboBOQuestion4] Between "0" And "7", 1, IIf([cboBOQuestion5] Between "0" And "7", 1, IIf([cboBOQuestion6] Between "0" And "7", 1, IIf([cboBOQuestion7] Between "0" And "7", 1, IIf([cboBOQuestion8] Between "0" And "7", 1, 0))))))))


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division