Question Help With A (Hopefully) Easy Expression...

[carrybag93]

I have three fields, 'Sub Total' (which is calculated by "[Quantity]*[Unit Cost]"), 'VIP Discount' (Yes/No data type) and 'After Discount'.

The VIP Discount is for exclusive customers. It takes 5% off the Subtotal.

I would like an expression to make up the 'After Discount' field. It should be the amount of the subtotal minus 5% of the subtotal, but only if the VIP Discount check-box is ticked. If the VIP Discount check-box is unticked, the 'After Discount' field should display the same amount as the Sub Total field. Is this possible? I have no knowledge of expressions at all.

 

[gemma-the-husky]

something like this will get close.

=iif(discount, [subtotal] * 1-[discount]),[subtotal])

is this in a query?

if so, add this as a column to your query, and change the field names to what you actually have.


----
with regard to the after discount field, you cannot have it both ways.
you cannot have it as a calculated field, and a stored value.

ie the above example creates a "new" column in the query from other information

if you want to STORE the caclulation, then you need to actively calculalte the value in a ofrm, before you leave the form.

All this is part of why the general recommendation is not to store "calculated values" - since they can easily be re-calculated when required

 

[pr2-eugin]

It is possible, I hope you are doing that in a Query.. All you need to do is go to the AfterDiscount column and type in ...

AfterDiscount: IIF([VIP Discount],([Subtotal]-([Subtotal]*0.05)),[Subtotal])

 

[carrybag93]

This is in a table. Could you please explain the elements in the expression? I don't know anything.

 

[carrybag93]

with regard to the after discount field, you cannot have it both ways.
you cannot have it as a calculated field, and a stored value.

If I do the expression in a table, can't I have the field calculate the data and then display something else?

 

[carrybag93]

It is possible, I hope you are doing that in a Query.. All you need to do is go to the AfterDiscount column and type in ...

AfterDiscount: IIF([VIP Discount],([Subtotal]-([Subtotal]*0.05)),[Subtotal])

How do I do this if I'm working in a table?

 

[carrybag93]

I figured it out with pr2-eugin's

AfterDiscount: IIF([VIP Discount],([Subtotal]-([Subtotal]*0.05)),[Subtotal])

...Thanks so much!