Subtotaling a calculated text box 4

[rss01]

I've inserted a calculated text box in the detail section of my report. I want to total this in the footer section. When I enter my calculation in the control source =SUM([TEXT81]) and run the report it wants me to enter a parameter value for TEXT81. Any ideas???

 

[CosmoKramer]

When computing a total with an aggregate function such as Sum you can't use the name of a calculated control in the Sum function. You must repeat the expression in the calculated control. So, repeat the calculation in your footer that appears in Text81.

If Text81 had:

=[Qty] * [Price]

then the control source in your footer would be:

=sum([Qty] * [Price])

 

[nickjar2]

Cheers Cosmo,

I have using access/vba for a few years and i never realised that. I always wondered why i had problems.

Nick

 

[rss01]

What I'm doing is calculating the percentage of the detail to the total. My detail text box has this calculation =[units]/[sum of units]. When I enter what you wrote =SUM([units]/[sum of units])I get the same message as before but now it wants to know the parameter value for [sum of units]???

 

[CosmoKramer]

So, the value you are trying to sum in the footer should basically be 100%, right?? Does [sum of units] come from a query??

 

[rss01]

Right. [sum of units] is a calculated text box summing the total of [units]

 

[CosmoKramer]

My initial thought was to hard-code "100%" for this field since it should always add up to 100%. But, try this for the control source in your footer:

=Sum([units]/DSum("units","YourTable","put your criteria here"))

Put your criteria to limit the DSum to only look at what field you are grouping on......

 

[DomP]

This is an interesting thread and it comes close (I think) to what I'm trying to do. Unfortunately I can't equate your suggestion to my exact circumstance. Sorry for the longwinded intro...

I have a calculated field =Sum([r_time])located in a subtotal of employee id. This field sums the hours worked for a particular time period. In that same group I have a total billable amount calculated as
=[Timekeeper_rate1]*[TOTAL_HOURS]The TOTAL_HOURS field is the name of =Sum(r_time).

I want the total of =[Timekeeper_rate1]*[TOTAL_HOURS] to appear in another following subtotal for projects but I can't get the syntax to work. I'm not sure if it's the syntax or the source query / sorting that is posing the difficulty. I've tried suming =[Timekeeper_rate1]*[TOTAL_HOURS]and I've tried summing the name given to this product calculation (TOTAL_DOLLARS) but it doesn't work.

Any help would be greatly appreciated.

Dom

 

[CosmoKramer]

Dom, Try this:

=DSum("[Timekeeper_rate1]*[TOTAL_HOURS]", "qryYourQuery")

I don't know if this will work in your situation, but it might be close....

 

[Guest_imported]

Cosmo,

Unfortunately not. What is the logic behind the qryyourquery code? I plugged in the supporting query with your code and it runs but I receive #error on that field. Since we're on the subject can you answer the following:

Summing the actual product with the syntax
=Sum[Timekeeper_rate1]*[TOTAL_HOURS]

or
Summing the name of the product with the syntax
=Sum([TOTAL_DOLLARS])

Are the above to allowed in Access. Rather confused here.

Thanks,

Dom

 

[CosmoKramer]

You should replace "qryYourQuery" with the query that's the RecordSource for your report. The goal of the DSum statement is to sum all "[Timekeeper_rate1]*[TOTAL_HOURS]" records in your query. If you need further criteria to limit your selection you can define it as a third parameter......

=Sum([TOTAL_DOLLARS]) can't be used....See my first response above....

 

[DomP]

Cosmo,

I'm getting the feeling this has something to do with the data rather than the syntax of the sum. I entered the dsum syntax and it doesn't work. The source query is pulling data from 4 tables. Is it possible I would have better luck by using separate queries and/or possibly a sub-report?
Or do you think that would have no impact?


Dom

 

[CosmoKramer]

Check out Access help for the RunningSum property. This should probably work for your situation.....

 

[DomP]

Cosmo,

Got it!!!!!!!!!!!!!! I seem to run sum and NOT display it and then reference that field in the following grouping and it works.

Thanks a bunch Cosmo!

Dom

 

[rayna]

rss01 -

You wrote:


When I enter what you wrote =SUM([units]/[sum of units])I get the same message as before but now it wants to know the parameter value for [sum of units]

Did you try =SUM([units])/SUM([sum of units])? Or would that give you what you want?

Rayna
Man plans. God laughs.

 

[rss01]

Whenever you do this you get a message box wanting to know the parameter value for [sum of units]

 

[rayna]

So - you have a text box named "units"?
And you have one that sums them called "sum of units" where the control source is =Sum([units])?

What about =Sum(([units])/sum([units]))?

Since it does not like the name of the second text box, perhaps it will be okay with the control source, instead.

Rayna
Man plans. God laughs.