group totals

  • Thread starter Thread starter Guldo K
  • Start date Start date
G

Guldo K

Hello.

I set up a report with two groupings.
When formatting the report I set some txtboxes to zero;
those txtboxes lie in group1-footer.
When formatting report body:
% Imponibile=round(Peso*Prezzo,2)
% Select case aIVA
% Case 0.04
% txtbox1 = txtbox1 + Imponibile
% Case 0.1
% txtbox2 = txtbox2 + Imponibile
% .....
% End Select
where Imponibile, Peso and Prezzo are recordset fields.

Then it ends up with 3 groups belonging to the 2nd group level;
OK with the first one, but the other two are wrong:
I realized that the first record in each of the two groups
is processed twice, so that the total in the group footer is
too high.
Why?

Just in case I've not been clear enough, the situation is
as follows:
*BeginReport*
Grouping1
Grouping2
record1 (OK)
record2
record3
NewGroup
record1 (twice)
record2
NewGroup
record1 (twice)
record2
record3
G2Footer
G1Footer
txtbox1
txtbox2
*EndReport*

The records marked '(twice)' are correctly
displayed only once in the report, but they're accounted
for twice, so that sum in txtbox1 is higher
than it should be.

Thanks,

Guldo
 
Guldo K said:
When formatting report body:
% Imponibile=round(Peso*Prezzo,2)
% Select case aIVA
% Case 0.04
% txtbox1 = txtbox1 + Imponibile
% Case 0.1
% txtbox2 = txtbox2 + Imponibile
% .....
% End Select
where Imponibile, Peso and Prezzo are recordset fields.
Click to expand...

Sorry... Imponibile is just a box in the report body,
without any source, of course; its source is
just that VBA formula...

*Guldo*
 
Guldo said:
I set up a report with two groupings.
When formatting the report I set some txtboxes to zero;
those txtboxes lie in group1-footer.
When formatting report body:
% Imponibile=round(Peso*Prezzo,2)
% Select case aIVA
% Case 0.04
% txtbox1 = txtbox1 + Imponibile
% Case 0.1
% txtbox2 = txtbox2 + Imponibile
% .....
% End Select
where Imponibile, Peso and Prezzo are recordset fields.

Then it ends up with 3 groups belonging to the 2nd group level;
OK with the first one, but the other two are wrong:
I realized that the first record in each of the two groups
is processed twice, so that the total in the group footer is
too high.
Why?
Click to expand...

The Format (and even Print) event may execute more than
once. A common reason it may be executed multiple times
(might be more than twice) is the KeepTogether property
where Access formats a section only to find out that it
won't fit on the page, so it starts a new page and has to
execute the format event again.

Regardless of the why, you can not use the Format or Print
events to calculate a value based on more than the current
record. It looks like you might be trying to sum up a value
depending on another value. If aIVA is a field in the
record source table/query, then you might be anle to
calculate textbox1 using the expression:

=Sum(IIf(aIVA = .04, round(Peso*Prezzo,2), 0)

If aIVA is not a field, then post back with more details and
we'll try to figure out how to calculate the result.
 
Marshall Barton said:
The Format (and even Print) event may execute more than
once. A common reason it may be executed multiple times
(might be more than twice) is the KeepTogether property
where Access formats a section only to find out that it
won't fit on the page, so it starts a new page and has to
execute the format event again.
Click to expand...

In fact I just set that property for that group.
Regardless of the why, you can not use the Format or Print
events to calculate a value based on more than the current
record. It looks like you might be trying to sum up a value
depending on another value. If aIVA is a field in the
record source table/query, then you might be anle to
calculate textbox1 using the expression:

=Sum(IIf(aIVA = .04, round(Peso*Prezzo,2), 0)
Click to expand...

aIVA is a field,
(IVA is the italian for Value Added Tax)
and I just meant to use the current record, each time.

I didn't want to use VBA, I just thought I needed it because
of the "italianization"...
I couldn't find a 'round' italian function. I thought I had to.
You know, I had to change your line into this:
=sum(iif(aiva=,04;round(peso*prezzo;2);0))
and as soon as I pressed enter Access changed it into:
=Somma(IIf([aiva]=0,04;round([peso]*[prezzo];2);0))

I was kinda worried because of the missed capitalization
of 'round', but it does work. Fine.

Thank you VERY much,

*Guldo*
 
Guldo said:
Marshall Barton said:
The Format (and even Print) event may execute more than
once. A common reason it may be executed multiple times
(might be more than twice) is the KeepTogether property
where Access formats a section only to find out that it
won't fit on the page, so it starts a new page and has to
execute the format event again.
Click to expand...

In fact I just set that property for that group.
Regardless of the why, you can not use the Format or Print
events to calculate a value based on more than the current
record. It looks like you might be trying to sum up a value
depending on another value. If aIVA is a field in the
record source table/query, then you might be anle to
calculate textbox1 using the expression:

=Sum(IIf(aIVA = .04, round(Peso*Prezzo,2), 0)
Click to expand...

aIVA is a field,
(IVA is the italian for Value Added Tax)
and I just meant to use the current record, each time.

I didn't want to use VBA, I just thought I needed it because
of the "italianization"...
I couldn't find a 'round' italian function. I thought I had to.
You know, I had to change your line into this:
=sum(iif(aiva=,04;round(peso*prezzo;2);0))
and as soon as I pressed enter Access changed it into:
=Somma(IIf([aiva]=0,04;round([peso]*[prezzo];2);0))

I was kinda worried because of the missed capitalization
of 'round', but it does work. Fine.

Thank you VERY much,

*Guldo*
Click to expand...


I am greatly relieved that it works, because because if were
a language local setting issue, I'd have been completely
lost.
 
Back
Top