Calculated textbox returns #error in subform

[sud2017]

Hi Experts,

I am using the below mentioned formula in a calculated textbox in a Subform for Sum of Item Qty * Item UnitCost but the textbox returns #Error. I have used this formula in another subform as well where it works fine.

I checked it without Sum function and the calculated textbox works fine.

Just a further information in case its relevant - the current subform has fields which has appended data from two different tables. Not sure if this could be a reason for SUM to not work.



Any insight will be greatly appreciated to resolve this. Thank you for your time.

=Sum(IIf([Currency]="CAD" And[OppID_FK]=1,[Item_UnitCost]*[Item_Qty],0))+Sum(IIf([Currency]="USD"And [OppID_FK]=1,[Item_UnitCost]*[Item_Qty],0))*[Parent].[Exchange]

[ranman256]

you should really put the formulas in the query.
Instead of IIF, sum both formulas, then do the IIF for which SUM you want to use.

Sum([Item_UnitCost]*[Item_Qty])
where [Currency]="CAD" And[OppID_FK]=1


Sum([Item_UnitCost]*[Item_Qty])
where [Currency]="USD"And [OppID_FK]=1

[June7]

This sure looks familiar. Wasn't this issue addressed in another thread couple months ago?

I don't see anything wrong with the expression. If the subform RecordSource includes the two tables, see no reason for the calc not to work.

[sud2017]

Thank you for looking into it Ranman256 and June7.

June7 - yes, you had helped me with this few months back and it worked perfectly in that particular subform and now I am using the calculated textboxes in a different subform. This subform has appended values based on an append query. Append query runs when I click save button. Query design can be seen in attached image. The formula is working when I remove SUM, so there seem to be something wrong with the SUM function.

It will be easier for me to copy and paste these calculated textboxes by changing OppID_FK for many such totals I want, but if putting it into the query itself is the right way, then I will do that.

Kindly advise. Thank you.

[June7]

The Sum expression should be in textbox in footer section. You show the subform in Datasheet view. The footer textbox will not display in Datasheet view. Use Continuous or Single view.

Or a textbox on main form can reference the subform footer textbox.

Probably would be best to calculate two fields with the IIf() in query. Then do the Sum() calc on form or report.

[sud2017]

Hi ranman256 and June7 - Thank you very much. My textboxes are working perfectly fine now. The only change I did is to add currency field in the append query, which wasn't there previously. I don't know if that solved the problem or something else, but I know that when I bring the question to experts here in the forum, solution comes very fast than when I struggle on my own.

You guys are helping many people to grow their skills and business. Thanks for your great support.