Serious flaw in Access 2000 Report writer?

[bojbrown]

I have a problem that Microsoft acknowledges as a bug, especially in print preview, but I can’t seem to find a workaround.

I have to produce a report that summarizes all transactions for a customer/day. The data is coming from a query. The data must be summarized for each customer, dividing the transaction amounts into one of six categories (hence six number columns on the report) that must subtotal and cross-foot.

The logic in the detail section examines the transaction code, classifies the transaction and adds it into the appropriate column. It then prints one line per customer (from the footer section). The six amount fields on the report are unbound, since the code classifies the amount for each transaction. It then adds it into the subtotal fields.

And this is where the problem occurs. The Access report writer actually passes through the section 3 times and thus the amounts are tripled. I have tried every work around I can think of, and the only one I found on MS (the FormatCount property) doesn’t work either.

There must be a simple solution to this problem, but I can’t find it and I have wasted many hours trying to resolve this problem. Can anyone help? (Is it me or is it MS?)

Thank you
Bruce

 

[o1110010]

And this is where the problem occurs... passes through the section 3 times and thus the amounts are tripled.

Is it always tripled? Could you figure out a third of the value and pass that to to the Access report writer?

 

[bojbrown]

Almost all of the time. There have been a customer's data that seems to go through 4 times (it also was the first one at the top of the second page).

 

[bojbrown]

Pat,

When I have tried the FormatCount, and it didn't work, I traced the logic. Both in the Detail Format section and in the Group format section, the subroutine repeated 3 times and in both cases, the FormatCount stayed at 1. which was surprising. I have since read on MS's knowledge base something to the effect that FormatCount is used for going to a second page. Is this correct? That might account for why it didn't change.

I have tried both Preview and diriect printing, and it still triples the totals. This is why I am so purplexed...it doesn't make any sense.

Do you know of any Hot Fix since SP3 that may have addressed this? I really am at a loss to know why this is happening.

I do appreciate your time...Thank you.

 

[bojbrown]

Update to what is happening for "Serious Reporting Flaw???"

I have an update to what is happening in the report:

I the FormatCount property stays at 1, however, the transactions themselves are repeating. For example:

for the first customer there are 3 txns for $10, $21, and $25. The detail section (which is does not print the individual txncode and amount) runs through the 3 txns in sequence. The detail section then runs through them 2 more times in sequence.

I have checked the data from the query, and the txns are only in there 1 time. Why would the detail format section process these 3 times. They are processed in sequence and then repeated in the same sequence 3 times. I have since tried changing the sorting, etc, to no avail, but this is why the totals are in there 3 times and the FormatCount property doesn't change, and thus can't stop this from happening.

At this point I'm thinking it must be something I've done if nobody has heard of this problem, but I have checked everything I can think of. If the Detail section is not visible, would this cause it to repeat for some reason?

Thanks for any one's help in this matter.

 

[bojbrown]

Thank you Pat,

I will take a look at the Retreat event.

The data cannot be summarized. Each transaction contains only one amount along with its accounting category, so processing each row is OK, becuase each row is checked for its category and added into the appropriate column, but as I mentioned, it is running each set of transactions for each customer thru 3 times. It processes them all correctly and when it reaches the last transaction, starts over again with that set, and then does it again. Freaky. (Does MS have a hot fix for gremlins?)

I have tried to add them in the group section, but that didn't work either. If you are interested, I could save the query and send the report to you. It would be interesting to see if you get the same results. If you don't, then for what ever reason, it's a bug in the installation.

Thank you for your continued help.

 

[ChrisO]

You may be able to wrap the calculation code in a static flag: -

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Static blnDone As Boolean
    
    If (blnDone = False) Then
        [color=green]' Do
        ' the
        ' calc[/color]
        blnDone = True
    End If
    
End Sub

Hope that helps.

Regards,
Chris.

 

[bojbrown]

That is correct. It is processing once for each row. That is not the problem.

The first customer has 3 detail transactions, thus 3 rows and it should run the detail section 3 times....it doesn't...it processes 9 times.