Print Report Footer Total value in Report Header

G

Guest

I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They do not
come out of a query (query is the control source for the report) due to
certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my Report
footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit


Now, I want to show the value of "granddollarperunit" in my report Header.

How can I do this?
 
M

MA

vrk1 said:
I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They do
not come out of a query (query is the control source for the report)
due to certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my
Report footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit


Now, I want to show the value of "granddollarperunit" in my report
Header.

How can I do this?
I'm not sure of what you need, but put a control in the footer with a
controlsource =Sum(txtcpu*amt)

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it
 
G

Guest

That did not work. Access prompts with "Enter Parameter Value" for txtcpu
and amt.

Please note that txtcpu and amt are calculated values in the report. They
do not come from the query.

Any other suggestions?

MA said:
vrk1 said:
I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They do
not come out of a query (query is the control source for the report)
due to certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my
Report footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit


Now, I want to show the value of "granddollarperunit" in my report
Header.

How can I do this?
I'm not sure of what you need, but put a control in the footer with a
controlsource =Sum(txtcpu*amt)

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it
 
M

MA

vrk1 said:
That did not work. Access prompts with "Enter Parameter Value" for
txtcpu and amt.

Please note that txtcpu and amt are calculated values in the report.
They do not come from the query.

Any other suggestions?

Yes calculate it again
if txtcpu = A+b

make sum((a+b)*amt)

MA said:
vrk1 said:
I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They
do not come out of a query (query is the control source for the
report) due to certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my
Report footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit


Now, I want to show the value of "granddollarperunit" in my report
Header.

How can I do this?
I'm not sure of what you need, but put a control in the footer with a
controlsource =Sum(txtcpu*amt)

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it
 
G

Guest

Thanks for your quick response.

See there is a problem there.

Control Source of my txtcpu is: =iif(tempbox>0,tempbox,cpu)

For some records, the value of txtcpu is tempbox. For others, the value is
cpu.
cpu is a field coming from the query and tempbox is an unbound field filled
out from a VBA code. Thats the complication involved here!

Now, How do I modify your formula sum((a+b)*amt) to suit this situation?

Or, Should I use a different approach here?




MA said:
vrk1 said:
That did not work. Access prompts with "Enter Parameter Value" for
txtcpu and amt.

Please note that txtcpu and amt are calculated values in the report.
They do not come from the query.

Any other suggestions?

Yes calculate it again
if txtcpu = A+b

make sum((a+b)*amt)

MA said:
vrk1 wrote:
I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They
do not come out of a query (query is the control source for the
report) due to certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my
Report footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit


Now, I want to show the value of "granddollarperunit" in my report
Header.

How can I do this?
I'm not sure of what you need, but put a control in the footer with a
controlsource =Sum(txtcpu*amt)

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it

--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it
 
M

Marshall Barton

vrk1 said:
I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They do not
come out of a query (query is the control source for the report) due to
certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my Report
footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit

Now, I want to show the value of "granddollarperunit" in my report Header.


Since you can not use an expression that contains only
fields in the report's record source table/query, you can
not use the Sum function to calculate the total.

Your appraoch of using the RunningSum property is valid in
calculating the total in the report footer section.
Depending on your version of Access (and maybe some(?) other
factors(?)), you might be able to use a header text box that
refers to the footer total text box.

A way that will work in all cases is to create a module
level variable (before any procedures) in the report's
module. Then, add a line of code to the footer's Format
event to save the total text box's value to the variable.

Additionally, you need to have a (invisible?) text box that
refers to the report's Pages property. The usual text box
expression =Page & " of " & Pages will do fine. This will
cause Access to process the report twice, once to calculate
the number of pages and again to insert that value where
needed. Taking advantage of that, a line of code in the
report header's Format event can then copy the saved value
from the variable to the header text box.
 
G

Guest

I agree with you in terms of creating a Public variable in a module and
transferring the Footer variable into the module's Public variable.

However, I am not able to understand why you suggested to create an
invisible text box to refer to report's pages property. Why should access
process the report twice?

Can you explain this concept please?

Thanks

Marshall Barton said:
vrk1 said:
I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They do not
come out of a query (query is the control source for the report) due to
certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my Report
footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit

Now, I want to show the value of "granddollarperunit" in my report Header.


Since you can not use an expression that contains only
fields in the report's record source table/query, you can
not use the Sum function to calculate the total.

Your appraoch of using the RunningSum property is valid in
calculating the total in the report footer section.
Depending on your version of Access (and maybe some(?) other
factors(?)), you might be able to use a header text box that
refers to the footer total text box.

A way that will work in all cases is to create a module
level variable (before any procedures) in the report's
module. Then, add a line of code to the footer's Format
event to save the total text box's value to the variable.

Additionally, you need to have a (invisible?) text box that
refers to the report's Pages property. The usual text box
expression =Page & " of " & Pages will do fine. This will
cause Access to process the report twice, once to calculate
the number of pages and again to insert that value where
needed. Taking advantage of that, a line of code in the
report header's Format event can then copy the saved value
from the variable to the header text box.
 
M

Marshall Barton

I thought I did explain that Access must process the report
once to calculate the total number of pages. Then the
second time through it can evaluate expressions containing
Pages with the value calculated the first time through.

Whether or not the Pages text box is invisible is your call.
--
Marsh
MVP [MS Access]

I agree with you in terms of creating a Public variable in a module and
transferring the Footer variable into the module's Public variable.

However, I am not able to understand why you suggested to create an
invisible text box to refer to report's pages property. Why should access
process the report twice?

vrk1 said:
I have a field in my detail section as follows:

Name: Dollarperunit
Control Source: =txtcpu*amt

Both txtcpu and amt are calculated fields within the report. They do not
come out of a query (query is the control source for the report) due to
certain reasons.

and an invisible field right below it as follows:

Name: invisibledollarperunit
Control Source: =[dollarperunit]
Running Sum: Over All



I have a field that shows the sum of all the dollarperunit in my Report
footer section as follows:

Name: granddollarperunit
Control Source: =invisibledollarperunit

Now, I want to show the value of "granddollarperunit" in my report Header.
Marshall Barton said:
Since you can not use an expression that contains only
fields in the report's record source table/query, you can
not use the Sum function to calculate the total.

Your appraoch of using the RunningSum property is valid in
calculating the total in the report footer section.
Depending on your version of Access (and maybe some(?) other
factors(?)), you might be able to use a header text box that
refers to the footer total text box.

A way that will work in all cases is to create a module
level variable (before any procedures) in the report's
module. Then, add a line of code to the footer's Format
event to save the total text box's value to the variable.

Additionally, you need to have a (invisible?) text box that
refers to the report's Pages property. The usual text box
expression =Page & " of " & Pages will do fine. This will
cause Access to process the report twice, once to calculate
the number of pages and again to insert that value where
needed. Taking advantage of that, a line of code in the
report header's Format event can then copy the saved value
from the variable to the header text box.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top