Not counting right with conditional formatting

[fla5hbak]

Hi there


I have nearly finished the database questionaire I am working on. Many thanks to SydneyGeek for the report suggestion.

Anyway, I have built a report with a large chunk of VBA code inside, which conditionaly formats the negative answers in the report in red, to give whoever is doing the anaylising a clear visual indicator of how "bad" the questionaire is.

Built into that code is also a variable called counter, which starts at 0 at the begining of the code and adds 1 to itself when ever a negative answer is selected. It then outputs to a text box at the bottom of the report.

The problem is, for whatever reason, it is not counting right, apart from on one record. The rest of the time, there is a difference between the amount of red formatted answers and the number displayed in the text box. The worst offender has 17 negatives, yet the text box reports it as having none....

Can anyone shed and light on the subject? I can post my somewhat un-efficient code if necessary.

Many thanks

Tref

 

[Andrew Fergus]

Hi Tref
Instead of using code, create an unbound text box in the group / section / report footer (you choose, any or all), with the following as the control source :
=Sum(IIf([value]<0,1,0))
Please note that I used the field [value] - make sure you use your actual field name for which you wish to count the negative values. Then you can use the standard conditional formatting from the menu to colour the negative values red. This should do the trick.
HTH, Andrew.

 

[fla5hbak]

Thanks Andrew, that worked a treat.

One question though. I split the negative answers boxes into three groups, and then put a total negative answers box in the footer. While the boxes on the main page worked fine, the total answers box in the footer just gave the random, inaccurate answers it had before. Any idea why?

Thanks again for your help

Tref

 

[Andrew Fergus]

Hi Tref
I'm not sure I understand what you meant by "I split the negative answers boxes into three groups". Did you use the same formula in a number of group footers? And is the report footer the same formula or is it something else?
Andrew