Odd report behaviour

I have a report which basically shows fees charged and fees paid. I use an
OnFormat of the detail to highlight the situation where FeesPaid <
FeesCharged. I also have a field "CountUnderpaid" which I increment. This
value is printed at the end of the report.

If I open the report and page through the report this value is reported
correctly. If I open the report and go straight to the last page,
CountUnderpaid is 1 too many

Any thoughts

Thanks

Phil

Re: Odd report behaviour

That's correct, Phil. Access only fires the events for the records that it
shows on screen. Consquently, if you try to programmatically accumulate a
value across multiple pages, the result can be wrong unless every page is
accessed.

The solution is to use other approaches rather than the event procedure. Add
a text box to the report footer, and set its Control Source to:
= - Sum([FeesPaid] < [FeesCharged])

This works because the expression is True (-1) when FeesPaid is less than
FeesCharged, False (0) when it is not, or Null if either field is Null. So
when you sum the field, you get the negative count of the cases where it is
True.

Similarly, you can use Conditional Formatting to change the color of the box
instead of the event procedure if you wish (assuming Access 2000 or later.)
CF is much faster than code in the event procedure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil Stanton" wrote in message
news:13p3g62rqcb6b84@corp.supernews.com...
>I have a report which basically shows fees charged and fees paid. I use an
>OnFormat of the detail to highlight the situation where FeesPaid <
>FeesCharged. I also have a field "CountUnderpaid" which I increment. This
>value is printed at the end of the report.
>
> If I open the report and page through the report this value is reported
> correctly. If I open the report and go straight to the last page,
> CountUnderpaid is 1 too many

Re: Odd report behaviour

Thanks Allen

Decided safer to put it all in the underlying query.

Was not aware that conditional formatting is faster. Have used both and
never noticed a difference. I assumed, obviously wrongly, that to all
intents and purposes CF generated a bit of "invisible" code which ran in the
background

Many thanks as ever for your help

Phil

"Allen Browne" wrote in message
news:4791c442$0$30874$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> That's correct, Phil. Access only fires the events for the records that it
> shows on screen. Consquently, if you try to programmatically accumulate a
> value across multiple pages, the result can be wrong unless every page is
> accessed.
>
> The solution is to use other approaches rather than the event procedure.
> Add a text box to the report footer, and set its Control Source to:
> = - Sum([FeesPaid] < [FeesCharged])
>
> This works because the expression is True (-1) when FeesPaid is less than
> FeesCharged, False (0) when it is not, or Null if either field is Null. So
> when you sum the field, you get the negative count of the cases where it
> is True.
>
> Similarly, you can use Conditional Formatting to change the color of the
> box instead of the event procedure if you wish (assuming Access 2000 or
> later.) CF is much faster than code in the event procedure.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Phil Stanton" wrote in message
> news:13p3g62rqcb6b84@corp.supernews.com...
>>I have a report which basically shows fees charged and fees paid. I use an
>>OnFormat of the detail to highlight the situation where FeesPaid <
>>FeesCharged. I also have a field "CountUnderpaid" which I increment. This
>>value is printed at the end of the report.
>>
>> If I open the report and page through the report this value is reported
>> correctly. If I open the report and go straight to the last page,
>> CountUnderpaid is 1 too many
>