Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Percentages

i have an order type system where a user enters in a product number

and it searches the database for that product and displays its

description and unit price, which works ok, but i am trying to

calculate the total as well as working out the discount

there is TWO problems

1. not all customers get discount so i keep getting an error

saying not divisible by zero if i enter "0" into the discount

textbox

2. How can i get the percentage equation working where it automatically

works out the percentage discount taken off the total

Private Sub txtTotal_Change(Index As Integer)

If IsNumeric(txtUnitPrice(Index)) And IsNumeric(txtQuantity(Index)) And Val(txtDiscount(Index)) > 0 Then

calculate = Val(txtUnitPrice(Index)) * Val(txtQuantity(Index)) + Val(txtDiscount(Index) / 100) * Val(txtTotal(Index))

txtTotal(Index).Text = calculate

End If

End Sub

PLEASE help

cheers

i have an order type system where a user enters in a product number

and it searches the database for that product and displays its

description and unit price, which works ok, but i am trying to

calculate the total as well as working out the discount

there is TWO problems

1. not all customers get discount so i keep getting an error

saying not divisible by zero if i enter "0" into the discount

textbox

2. How can i get the percentage equation working where it automatically

works out the percentage discount taken off the total

Private Sub txtTotal_Change(Index As Integer)

If IsNumeric(txtUnitPrice(Ind

calculate = Val(txtUnitPrice(Index)) * Val(txtQuantity(Index)) + Val(txtDiscount(Index) / 100) * Val(txtTotal(Index))

txtTotal(Index).Text = calculate

End If

End Sub

PLEASE help

cheers

Where are you getting the error that you canot divide by 0, as the code you show MULTIPLIES by 0. Why would you ever be dividing by the DISCOUNT percentage?

And the formula that you show should be correct (except for the SIGN (+/-) issue noted above), even in the case of a Discount percentage of 0, so there is no real reason to only allow txtDiscount >0, as you appear to be doing.

Arthur Wood

when a Discount is applied to a sale:

Real Amount = Quantity_Sold * (Base_Price * (1 - Discount/100))

so you should be SUBTRACTING the Discount percentage from 1, then MULTIPLY that factor by the Unit Price, and then By the Number of Items Sold (at the Discounted Price)

Arthur Wood

Where is "calculate" sub?

Val(anything) does *not* return correct value on my regional setting. Use correct conversion functions, like CDbl, CCur... and function IsNumeric to check if entered value is numeric.

Anyway . . .

I think your question is how to handle those pesky ZEROS

DiscountedAmount = SubTotal / iif(discount = 0, 1, discount)

Any amount divided by 1 is no discount, otherwise . . .

Well you get the picture.

Good luck -

<`{{{><

Gene

thanks for your comment, could you post a sample showing how i can do this using code, i am a student and havn't got to grips with VB yet!

thanks for all the help!

DD66

Arthur_Wood, your code is not locale aware. Do not use Val().

If Discount is 7.5%, and if decimal separator is comma (e.g. in Germany), txtDiscount will hold "7,5"

? Val("7,5")

7 ' not OK

Use CDbl() or CCur() :

Calculate = CDbl(txtUnitPrice(Index).T

(1 - CDbl(txtDiscount(Index).Te

Val() will work OK only for whole numbers, which do not have thousand separator.

? Val("2000")

2000 ' OK

? Val("2.000")

2 ' not OK

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Dim Calcluate as Currency

If IsNumeric(txtUnitPrice(Ind

calculate = Val(txtUnitPrice(Index)) * Val(txtQuantity(Index)) *( 1 - Val(txtDiscount(Index) / 100)

txtTotal(Index) = Format$(Calculate, "$#,##0.00)

Else

txtTotal(Index) = "Invalid Amount"

End If

and I would call this from each of the Validate Event handler procedures for the txtUnitPrice(index), txtQuantity(index) and txtDiscount(Index) text boxes - so that if ANY of those three values is changed, you recalculate the total for THAT line.

then is, something like this:

Private Function CalcTotal(Index as Integer) as Integer

Dim Calcluate as Currency

If IsNumeric(txtUnitPrice(Ind

calculate = Val(txtUnitPrice(Index)) * Val(txtQuantity(Index)) *( 1 - Val(txtDiscount(Index) / 100)

txtTotal(Index) = Format$(Calculate, "$#,##0.00)

CalcTotal = 0

Else

txtTotal(Index) = "Invalid Amount"

CalcTotal = -1

End If

End Sub

then you would code this sort of like this:

Option Explicit

Private Sub txtUnitPrice_Validate(Inde

Cancel = CalcTotal(Index)

End Sub

Private Function CalcTotal(Index As Integer) As Integer

Dim Calculate As Currency

If IsNumeric(txtUnitPrice(Ind

Calculate = Val(txtUnitPrice(Index)) * Val(txtQuantity(Index)) * (1 - Val(txtDiscount(Index) / 100))

txtTotal(Index) = Format$(Calculate, "$#,##0.00")

CalcTotal = 0

Else

txtTotal(Index) = "Invalid Amount"

CalcTotal = -1

End If

End Function

Private Sub txtQuantity_Validate(Index

Cancel = CalcTotal(Index)

End Sub

Private Sub txtDiscount_Validate(Index

Cancel = CalcTotal(Index)

End Sub

make sure that each ot the textboxes is loaded with a DEFAULT Text value of 0 (this is done by making the Text property of each text box, in desing mode, 0 - the number 0)

see if that gets you started.

Arthur Wood