how do i show £0.00 in a Total Discounted Price field when the Discount is 0?

[baxton28]

Hi Everyone, been a while since I've used access and stumbled on a quandary.
I'm setting up a costings Db for my business and I have gotten stuck on an issue with discounts and total discount price

I have a costings form, with client details and also totals to be populated from a materials subform run on a qry.
issues I'm having is on the discounts side.

Fields:
[Price_ea] [QTY] [Totalcost] [Discounts] [totalDiscPrice]

so if I enter a [Price_ea] £5.00 [QTY] of 1 [totalcost] £5.00 [discount] 10% [totalDiscPrice] £4.50
the above is ok
If I don't have a discount% on a price, which will be 0 in the field, the [TotalDiscPrice] shows the [Totalcost] so when I come to calculate the the whole of the [TotalDiscPrice] its including the the amounts which has no discount.
so i suppose long story short, how do i show £0.00 in the TotalDiscPrice when the Discount is 0?



formulas
[Price_ea]*[QTY] = [TotalCost]
TotalDiscPrice: ([TotalCost]-[TotalCost]*[Discounts]/100)

hope this makes sense?
kind regards Baxton
Access 2013 win11

[mike60smart]

Hi

Try: TotalDiscPrice: ([TotalCost]-[TotalCost]*Nz([Discounts],0)100)

[baxton28]

Hi Mike thankyou for replying to my thread, ok i just had a go,ive inputted the NZ as suggested in the qry and the TotalDiscPrice still displaying the TotalCost amount...hummmm

[mike60smart]

Hi

Sorry my mistake

Looking at your screenshot it looks like you have entered the discount values as whole values vice Percentages.

The Discounts should be set using a combobox

The Formulae you need is as follows:- =[TotalCost]-([TotalCost]*[cboDiscount].[Column](1))

[Micron]

maybe just TotalDiscPrice: IIF([Discounts]=0,0,[TotalCosts]-[Discounts])

I can't tell how you're calculating the discount amount so the bold part probably needs adjusting.

[mike60smart]

Hi

If you can upload the database so we can see the structure of the Form it would help.