Summing fields in subforms 1

[Gigi0614]

Hi!

I'm not sure if I can do this. I've got a main form, and I'd like to created a calculated field that sums some values in one of my subforms.

Can that be done??

 

[PHV]

Take a look at the DSum VBA function.
Or, if the subform is continuous, create the calculated control in the header or footer section with the Sum SQL aggregate function. Your main form may grab the calculated value with this syntax:
Me![subform control name].Form![calculated control name].Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gigi0614]

Hi PHV,
I looked up the DSUM syntax and here is what it said to do:

DSum («expr», «domain», «criteria»)

I'm not sure how to do this...

My expr = addCharges.cost
domain = addCharges

And here is where I'm confused... My criteria is a pretty nasty join between 3 tables:

SELECT addCharges.cost
FROM scheduledSurgery INNER JOIN (addCharges INNER JOIN surgeryAddCharge ON addCharges.addChargeID=surgeryAddCharge.addChargeID) ON scheduledSurgery.surgeryID=surgeryAddCharge.surgeryID;

How would you format that??

Thanks!!

 

[PHV]

By using a saved query doing the 3 tables join.
This query will be the «domain»
The criteria will probably be a condition on surgeryID.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gigi0614]

PHV,
Would you be interested in helping me finish this project? It's clearly over my head, and I'm under a time crunch...

I just have a few calculated control fields on a subform that are sums of child records. Should be easy to do. Also, I've got a report that I need help with getting it to break correctly and sum several total fields.

Please let me know if you are interested, and what your hourly rates are.

THanks!!

 

[PHV]

I've got a report that I need help with getting it to break correctly and sum several total fields
I suggest you posting in the Access reports forum:

http://www.tek-tips.com/threadminder.cfm?spid=703&newpid=703

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244