Calculated Textbox considering different item currencies

[sud2017]

Hello Guys,

I have a calculated textbox 'Total Cost' in my main form, which displays Sum of Unit cost*Qty of items from the items subform. Now, I have included 'Currency' (USD and CAD) field in my items subform as the items currencies differ. There is an Exchange rate textbox default value in the main form.

How do I consider currency in my Total Cost calculation? I wish to show Total Cost in both USD and CAD.

Is it something to do with textbox formula (if/else statement) or something else?

Thank you indeed for any possible help.

[June7]

So some items are USD and some CAD in the same order and therefore some items need conversion? Let's say the exchange is $1USD = $1.25CAD. If the rate textbox holds 1.25, consider:

TotalCAD = Qty * cost * IIf(Currency = "USD", [Parent].[rate], 1)

TotalUSD = Qty * cost / IIf(Currency = "CAD", [Parent].[rate], 1)

Since exchange rates tend to change over time, should probably save the rate in effect at the time the order is calculated.

[sud2017]

Thanks indeed. Here is what I have now for TotalCAD, which is giving me #Error in that main form textbox:

=Sum([ItemsTsubform].[Form]![Item_Qty]*[ItemsTsubform].[Form]![Item_UnitCost]*IIf([ItemsTsubform].[Form]![Currency]="USD",[Exchange],1))

[June7]

Suggest you give the subform container a name different from the object it holds, like ctrItems.

Do the Sum calc in a textbox in the subform:

=Sum([Item_Qty]*[Item_UnitCost]*IIf([Currency]="USD",[Parent].[Exchange],1))

Then textbox on main form references the subform textbox:

=ctrItems.Form.[textboxname]

Only way I can get it to work.

[sud2017]

Thanks again. It seems pretty strange to me that the below formula works fine for CostCAD for cost of individual items but when used with Sum function in my subform footer it doesn't return any value.

This works for cost of individual items:
=[Item_UnitCost]*[Item_Qty]*IIf([Currency]="USD",[Forms]![QuoteEntryForm]![Exchange],1)

This doesn't work for sum of item costs (in subform footer):
=Sum([Item_UnitCost]*[Item_Qty]*IIf([Currency]="USD",[Forms]![QuoteEntryForm]![Exchange],1))

Is there a way to check if the above textbox in subform footer returns sum value? Currently, I am looking at the TotalCostCAD textbox in my main form that is referenced to it.

[June7]

Actually, my test did not include multiplication of field or control from main form within aggregate function, just how to capture aggregation of a field in the subform. Aggregate functions cannot reference controls, only fields. This could get a bit more complicated.

TotalUSD:
=Sum(IIf(Currency="USD", [Item_UnitCost]*[Item_Qty], 0)) + Sum(IIf([Currency]="CAD", [Item_UnitCost]*[Item_Qty], 0)) / [Parent].[Exchange]

Note the literal use of [Parent] as substitute for the full form qualifier.

[sud2017]

Still not quite working. When I remove SUM function from your formula, it returns some value, so apparently there's something with the SUM function. UnitCost, Qty and Exchange are all 'fields'. Thank you again for your time.

[sud2017]

I got it working now. I had the formula in subform footer instead of details where my item details exist. Your formula works perfectly. Much appreciated. Thanks again.

[June7]

I would expect it to be in the subform footer. Oh well, as long as you get the desired result.