Running Sum on Calculate Bound Text Box

[Jtrim52]

I have a field that calculates charges based on number of days times rate. Days and rates are actual MS Access Fields, the result is arrived at in a Bound Text Box as a calculated field. How dow I sum the calculated fields on a report?

 

[dhookom]

Sum the undisclosed expression/calculation. In a Report or Group Header or Footer section, you can use a control source like:
=Sum([FieldA]*[FieldB]-[FieldC])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crabback]

I have a simular problem to this, and I had thought dhookoms solution would work BUT my calculated field used a dlookup and I cannot do a sum on a dlookup. Here's what I am trying to do...
=SUM(DLookUp("sum([CurrentExposure])","treaty")/[xchngRte])
but I can't use the outer sum(). Does anyone know a way around this?

thx

Crabback

 

[dhookom]

Where is you Sum() located in your report? DLookup() probably returns some sort of variant data type. You might want to try:
=SUM(Val(DLookUp("sum([CurrentExposure])","treaty"))/[xchngRte])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crabback]

Thanks dhookom
I tried that but got 'error' on the rpt preview.
In my detail section I have a txt box called txTEur which is unbound, control source set to:
=[txTX]/[xchngRte]
-- 'txTX'; uses the dlookup function to lookup CurrentExposure on the Treaty table.
txTEur reads this value and divides it by the xchngRte to get the Euro equivalent.
I want to get the total euro amount -- sum(txTEur) -- on the report footer. But I get errors when I try sum(txTEur). So i tried to bring it back to
=SUM(DLookUp("sum([CurrentExposure])","treaty"))/[xchngRte])
I've been trying to crack it through the vb but I'm getting nowhere.
Any ideas?
appreciate yr help!

Crabback

 

[crabback]

Got it! I created a hidden unbound txBox in my detail section set to the txBox containing the value I wanted to total (txTEur) and set running sum to over all.
All I had to do then was set the txbox in my rpt footer to the new hidden txBox.
-- works for sums but I wonder what if I wanted to get overall max???
thx for your help Duane.

Crabback

 

[dhookom]

I would create a totals query

SELECT Sum(CurrentExposure) as TotCurExp
FROM treaty

This will return the single value that you need in your report without using the horrible DLookUP. Just set your control source to:
=[TotCurExp]/[xchngRte]
You can use
=Sum([TotCurExp]/[xchngRte])
To sum the expression.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]