Summing problem on report

[Hank]

I have a report-summing problem using Access 2000. When a section runs
over the end of the page, sometimes a detail gets picked up twice.

Example:
Customer Header
XYZ Company
Detail Section (INVISIBLE)
Invoice 1 $100.00
Invoice 2 $250.00
Page Break
Customer Footer
Customer Total $450.00

The bad total is because Invoice 1 is summed twice.

Keep section together does not help. If I make the detail section
visible, then the page fills in a different manner and the problem goes
away. There are no sub-reports on this report

This is a very rare occurrence but I do see it once in awhile. How
should I change my report to solve this?
Hank Reed

[Allen Browne]

Hank, how are you calculating this total?

Are you using code in the (Format?) event of the Detail section to
accumulate the total as a variable? If so, drop the code and set the Control
Source property of the Customer Total box to:
=Sum([Amount])

If that does not solve the problem, what is the RecordSource of the report?
Is there anything there that could cause one row to show up multiple times?

If that still does not pin down the issue, check your updates of Access and
JET. Locate msaccess.exe on your hard disk, typically in:
C:\Program Files\Microsoft Office\Office
Right-click the file, and choose Properties.
On the Version tab, if you do not see 9.0.0.6620, go to:
http://support.microsoft.com/gp/sp
and download SP3 for Office 2000.

Just as important, locate msjet40.dll, typically in \windows\system32.
You should see version 4.0.8xxx.0.
The digits at xxx don't matter, but if you don't see the 8, get SP8 for Jet
4 from the same link as above.

--
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.

"Hank" <ha********@aol.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I have a report-summing problem using Access 2000. When a section runs
over the end of the page, sometimes a detail gets picked up twice.

Example:
Customer Header
XYZ Company
Detail Section (INVISIBLE)
Invoice 1 $100.00
Invoice 2 $250.00
Page Break
Customer Footer
Customer Total $450.00

The bad total is because Invoice 1 is summed twice.

Keep section together does not help. If I make the detail section
visible, then the page fills in a different manner and the problem goes
away. There are no sub-reports on this report

This is a very rare occurrence but I do see it once in awhile. How
should I change my report to solve this?
Hank Reed

[Hank Reed]

Thanks Allen.
No, I have never used VBA for summing in the detail section –
doesn’t work well

Typically, I accumulate totals like this:
Variable Variable
Visible Invisible
IV 1 $100 [IVTotal] [Running] = [IVTotal] Over Group $100
IV 2 $250 [IVTotal] [Running] = [IVTotal] Over Group $350
IV 3 $ 75 [IVTotal] [Running] = [IVTotal] Over Group $425

Section Footer
Total of all Invoices [GrandTotal] = [Running] = $425

This might be replicated over several section groupings: Date, Customer,
Invoice

I believe the recordsource is fine. Remember this only happens on a
page rollover and not very often at that.

I did find msjet40.dll but did not know how to find the version number.
Hank Reed

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Okay, you're using Running Sum. That should work but you could try the text
box with:
=Sum([IVTotal])
or whatever the field name is called.

Right-click the msjet40.dll, and choose Properties.
The 2nd tab of the Properties box is Version.

--
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.

"Hank Reed" <ha********@aol.com> wrote in message
news:uO**************@news.uswest.net...

Thanks Allen.
No, I have never used VBA for summing in the detail section â?"
doesnâ?Tt work well

Typically, I accumulate totals like this:
Variable Variable
Visible Invisible
IV 1 $100 [IVTotal] [Running] = [IVTotal] Over Group $100
IV 2 $250 [IVTotal] [Running] = [IVTotal] Over Group $350
IV 3 $ 75 [IVTotal] [Running] = [IVTotal] Over Group $425

Section Footer
Total of all Invoices [GrandTotal] = [Running] = $425

This might be replicated over several section groupings: Date, Customer,
Invoice

I believe the recordsource is fine. Remember this only happens on a
page rollover and not very often at that.

I did find msjet40.dll but did not know how to find the version number.
Hank Reed

[Hank]

My tests with the Sum function in a report indicate that it will sum a
bound text box but not a text box with a calculated value in it.
For instance, I could sum [Price] or [Quantity] but not sum a text box
named [ExtendedPrice], that held [Price] * [Quantity].

When I set the Control Source to =[ExtendedPrice] is pretends not to
know what text box I'm talking about. The Text Boxes [Price],
[Quantity], and [ExtendedPrice] are all in the detail section.
I want the sum of [ExtendedPrice] in the footer.
Hank Reed

[Arno R]

You will need the syntax = Sum([Price]*[Quantity]) here ...

Arno R
"Hank" <ha********@aol.com> schreef in bericht news:11**********************@g14g2000cwa.googlegr oups.com...

My tests with the Sum function in a report indicate that it will sum a
bound text box but not a text box with a calculated value in it.
For instance, I could sum [Price] or [Quantity] but not sum a text box
named [ExtendedPrice], that held [Price] * [Quantity].

When I set the Control Source to =[ExtendedPrice] is pretends not to
know what text box I'm talking about. The Text Boxes [Price],
[Quantity], and [ExtendedPrice] are all in the detail section.
I want the sum of [ExtendedPrice] in the footer.
Hank Reed

[Allen Browne]

Arno's is probably the simplest solution, and should work 100%.

Another solution is to create a query, and put the calculation into the
query as a calculated field, e.g.:
ExtendedPrice: [Price] * [Quantity]
Using the query as the RecordSource for your report, you can then use:
=Sum([ExtendedPrice])

--
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.
"Arno R" <ar***********@tiscali.nl> wrote in message
news:42*********************@dreader2.news.tiscali .nl...
You will need the syntax = Sum([Price]*[Quantity]) here ...

Arno R

"Hank" <ha********@aol.com> schreef in bericht
ews:11**********************@g14g2000cwa.googlegro ups.com...

My tests with the Sum function in a report indicate that it will sum a
bound text box but not a text box with a calculated value in it.
For instance, I could sum [Price] or [Quantity] but not sum a text box
named [ExtendedPrice], that held [Price] * [Quantity].

When I set the Control Source to =[ExtendedPrice] is pretends not to
know what text box I'm talking about. The Text Boxes [Price],
[Quantity], and [ExtendedPrice] are all in the detail section.
I want the sum of [ExtendedPrice] in the footer.
Hank Reed

[Hank]

I have always known that the sort order in the "Sorting and
Grouping" dialog box takes preference over sorts you may specify on the
report, including sorting the recordset.
I did, however, miss the point that there is a "Keep Together"
choice in "Sorting and Grouping". This takes preference over the
"Keep Together" selection on the properties box for the report section.
Having set "Keep Together" for my Customer section (in the
Sorting and Grouping Dialog), my report totals accurately but I may be
just masking the bigger problem.
Thanks to all,
Hank Reed