Error on Calculated field. (1 Viewer)

[jdcallejas]

Hello,

I have a form called menu, here I create menu plates like Roast beef example. Roast beef is served with rice, bean, etc. When I create the menu, I add each ingredient that the plates use on a subform. When I pull the product ingredient it brings with it the average cost of the product.

So on the subform, I multiply the average cost by the amount I use for that plate. This calculation works fine for each individual ingredient. On the subform footer, I want to sum all ingredients on the subform calculated field. So I do a Sum([average]*[Quantity]) but I get an error.

I have the something working fine in the PO form, but here there is something wrong I can't figure out..,

Can you take a look at it.. Its the frmMenu

Thank you

 

[theDBguy]

Hi. Didn't look at your attachment but is [average] a calculated Textbox? If so, try repeating the calculation instead of referring to the Textbox.

Sent from phone...

 

[Micron]

I don't think you can sum a calculated control - you have to sum or DSum over a field. In that case, perhaps best to make the calculation in a query and sum that field (not the control on the form). You have based your form on the table. I almost always base forms on queries because of their flexibility.

 

[jdcallejas]

Thank you,

Ok, this is what's going on. The ProdQuant and the AverageCost are not calculated fields on the subform. So for me to get the Ingredient cost I multiply AverageCost by ProdQuant. I do this in an unbound field on the subform detail and I get each ingredient cost for that plate. Where I am having issues is when I want to Sum all the ingredients cost to get an overall cost of the plate I am creating... I have tried many ways as you said but I can't get the Sum to show up.....

 

[jdcallejas]

Here is the way I have it to get each ingredient cost for that plate

Here is the formula working. What I need to get its the total of numbers on the right hand side of the subform...