total on calucalted fields

[frustrated058]

I have a calculated field in a form. How do I get a total of that field at the bottom of the page? I tried putting and unbound box in the form's footer with =sum[totalvalue] and I get #Error.

 

[KenReay]

HI

Say [TotalValue] is [Value1]+[Value2], then do Sum([Value1]+[Value2])

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[frustrated058]

Thanks, but that still gives me the #Error message.
Let me give you a little more detail. I am tracking inventory. I have several tables, but the two that affect this formula are the "Products" table which has a list of all of the "items", thier "upc codes", and "unit prices". The other table is the "Transaction" table which lists all of the inventory items in and out by both "quantity in". "quantity out" and "weight in", "weight out". Then I have set up a "Products Subform" which brings in all of the informaiton from the "Product" and "Transaction" tables onto one page. In the "Product Subform" footer I have some formuala's which total the quatity and wieghts of each item. The one is called "WeightOnHand" and has a formula =Sum(nz([WeightIn])-nz([WeightOut])). This one works fine.
Then I have a form called "Products Value". It takes the "Item", "UPC", "WeightOnHand" and "Unit Price". I added a unbound field called "TotalValue" It has a formula that reads =[WeightOnHand]*[Unit Price]. This also works fine - giving me one line of information on each item of inventory with it's total weight and value.
The problem comes when I want to total all of these "TotalValue" at the bottom of this form. It is probably an easy solution, but I sure can't figure it out. Does it matter that "GrandTotal" in the footer is being derived from a calculated field ("TotalValue&quot which is being derived from a calculated field ("WeightOnHand&quot?

 

[pooksa]

Your calculated grand total has to be in the form footer - not the detail section or any group footer.

You would use the same formula you used for TotalValue, except when used in the form footer, it will sum all the values together instead of on each record.

=Sum([WeightOnHand]*[Unit Price])

(You can't use =SUM([TotalValue]) since TotalValue is unbound.)

HTH
Chris

 

[frustrated058]

Thanks, but I am in the form footer and this formula still gives me the #Error message.

Thanks
Luann

 

[pooksa]

Is the data source for your
""Products Subform" which brings in all of the informaiton from the "Product" and "Transaction" tables onto one page"
derived from a query? Or is the control source for each field drawn from one of the two tables?

Chris

 

[jebry]

Hi Luann!

Your problem is that you can't access fields on a subform directly from the main form. You need to use:

Me!SubFormControlName.Form!ControlName

Now for the other problem. You cannot use the names of calculated controls in an aggregate function like Sum. Access requires that you repeat the calculation in the Sum. It seems uncertain if this will work in the situation you describe. If it doesn't then maybe you can do the calculations in a query and use a DSum instead.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[frustrated058]

Thanks for your patience!
To answer your questions- The information in the "Products Subform" is all fields directly linked to the tables (no query). The only calculated fields on that subform are the two in the footer which give me the total "WeightOnHand" and QuantityOnHand" for each item.
My subform is named "Products Subform" and the form that I am trying to do the total calculation on is "Product Value". So do I understand you correctly? I need to use

Me!Products Subform.Product Value
Where do I put this?

Also, I need to multiply "WeightOnHand" times "UnitPrice" before I can sum it (if I cannot use the "TotalValue" field to get my grand total). "Unit Price" is a bound box that comes from a table. But "WeightOnHand" comes from an unbound box with a calculated value in the subform. Are you saying that I need to use the whole formula from the "WeightOnHand"
=Sum(nz([WeightIn])-nz([WeightOut]))

So will it look like this?
=Sum(nz([WeightIn])-nz([WeightOut]))*([UnitPrice])
At this point, that still gives me the #Error message - but I have not put in the Me! statement yet, since I don't know where to put it.

Thanks
Luann

 

[jebry]

Hi LuAnn!

The correct syntax is this:

Me![Products Subform].Form![Product Value}

And you would use it here:

=Sum(nz(Me![Products Subform].Form![WeightIn])-nz(Me![Products SubForm].Form![WeightOut]))*(Me![Products Subform].Form![UnitPrice])

Again, I have my doubts if Access will accept even this. The best solution is to transfer the calculation to a query and use domain functions, in the following form:

DSum("YourQueryField", "YourQuery", "YourCriteria&quot

hth


Jeff Bridgham
bridgham@purdue.edu