apply % discount to currency value

[mattyj1990]

apply % discount to currency value in text box

I have a table called discounts which has the following columns;
ID;Name;Discount Amount;Description

on a form i have a combo box which lists the columns on the table.

What i want to do is apply the discount that is in the third column it is ok when the discount is money off, £30 off, but some of my discounts are percentages, 5%. How do i apply this discount

 

[RuralGuy]

How do you determine if it is a flat rate discount or a percentage discount? What control on the form gets discounted and where do you put the results?

 

[Luddite Lad]

(Retail Value)*(100-Discount)/100

 

[mattyj1990]

Please Look At The Word Document Attached It Explains My Situation

 

[RuralGuy]

How about adding two fields to your Discount table? One is a True/False field named PercentField and the other is a Single numeric field named Discount for the value. If Instr([Discount Amount],"%") <> 0 Then PercentField = True and Discount = CSng(Left([Discount Amount],Len([Discount Amount])-1))
otherwise Discount = CSng([Discount Amount])

 

[mattyj1990]

How about adding two fields to your Discount table? One is a True/False field named PercentField and the other is a Single numeric field named Discount for the value. If Instr([Discount Amount],"%") <> 0 Then PercentField = True and Discount = CSng(Left([Discount Amount],Len([Discount Amount])-1))
otherwise Discount = CSng([Discount Amount])

Sorry rural guy but can you explain this more as i do not quite understand

 

[RuralGuy]

Are you allowed to add fields to your table? If not then this could be done with a query.

 

[mattyj1990]

Are you allowed to add fields to your table? If not then this could be done with a query.

no the fields will not change but will it matter if the table has records changed and deleted when needed or not?

 

[RuralGuy]

no the fields will not change but will it matter if the table has records changed and deleted when needed or not?

If you use a query and as long as the format of the fields remains the same then it will not matter whether records are added or deleted. Can you use the query builder to create a query of the Discount table and add two new fields to the query?

 

[mattyj1990]

Sorry RuralGuy i bet you think i am a dumb guy but im not 100% sure how. Please explain more if you want my email address to reply send me a private message

Thanks

 

[RuralGuy]

Sorry RuralGuy i bet you think i am a dumb guy...

Not even close. We're just in an area that is new to you. Let's start by creating a query of the table that includes all of the fields in the table. Let me know when you have done that. Let's keep this public to benefit others that have not done this.

 

[mattyj1990]

Ok RG Done the Query

 

[RuralGuy]

Now open the query in Query Design mode and move to a new column in the Field slot. Type in the following:
PercentField:=IIF(Instr([Discount Amount],"%") <> 0, True, False)
...assuming that the other field name is [Discount Amount]. Then look at the DataSheet view of the query and see if this new field works as we expect.

 

[mattyj1990]

Ok done that it shows -1 for a percentage and 0 for a standard value.

 

[RuralGuy]

GREAT!! Now go to another new column in the Field slot and type in the following:
Discount:=IIF(Instr([Discount Amount],"%") <> 0,CSng(Left([Discount Amount],Len([Discount Amount])-1))/100, CSng([Discount Amount])) and then switch to DataSheet view and look at the results. Assuming no typos then the PercentField=True rows should have some decimal value like .3 or something.

 

[mattyj1990]

Yes its working perfectly

 

[RuralGuy]

Almost there! Now use this query as the RowSource of the ComboBox on your form. We'll apply the discount in the AfterUpdate event of the ComboBox control. What is the name of the control with the undiscounted price in it and what is the name of the control where you want to put the discounted price?

 

[mattyj1990]

the Combo box is called "Discount1"
the total before discount is "A"
and
the total after discount is "AA"

 

[RuralGuy]

Is [PercentField] in 4th column and [Discount] in the 5th column of the Discount1 ComboBox?

 

[mattyj1990]

Is [PercentField] in 4th column and [Discount] in the 5th column of the Discount1 ComboBox?

[PercentField] is 6th
[Discount] is 7th