Sum not collecting Field data

[cneill]

Hi,

Got problem, the set up is as follows:-
In Report Footer
Text Box (which is not visible to the user)
Name - TCO2
Control Source =DLookUp("[PDTCO2]","[QryNorms]","[PDTCO2]")
The value it looks up is 1.08585

Name - TotalTCO2
Control Source =Sum([TotalPD]*1.08585)/1000
This all works but what I would like to do for the TotalTCO2 Text Box is to change the Control Source to =Sum([TotalPD]*[TCO2])/1000 so that it takes the value from the TCO2 Text Box. When I try this I get an Error.
Can anyone suggest a solution?

Thanks Neill

 

[genomon]

First of all you have =DLookUp("[PDTCO2]","[QryNorms]","[PDTCO2]"). Don't understand why there is [PDTCO2] as a WHERE condition? That being said, how about:

Sum([TotalPD]*DLookUp("[PDTCO2]","[QryNorms]"))/1000

If [PDTCO2] can ever be null, you may want to Nz() it.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

How about:

=Sum([TotalPD])/1000 * Val([TCO2])

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

Nice one as usual, Duane!

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

I'm not sure it will work. I was concerned about recalcing TCO2 inside the Sum().

Duane
Hook'D on Access
MS Access MVP

 

[cneill]

Hi Everyone,

Thanks for the ideas, still getting error, any other thoughts

Thanks

Neill

 

[cneill]

Hi Everyone,

Sorry
Sum([TotalPD]*DLookUp("[PDTCO2]","[QryNorms]"))/1000
works great if I cut and paste it correctly!!!!

Thanks for all your help

Neill