Solved

percentages

Posted on 2002-03-27
8
513 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:digitaldestruction66
8 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6899195
the formula that you show in the code snippet will ADD the "discount" to the total, not subtract (a DISCOUNTis usually subtracted from the base amount, not added.

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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6899234
in fact the formula that you show is VERY wrong:

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
0
 
LVL 15

Expert Comment

by:ameba
ID: 6899338
Why do you use txtTotal_Change event to calculate things?
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.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 1

Expert Comment

by:genehead
ID: 6899483
Sometimes dividing works for discounting . . .

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
0
 

Author Comment

by:digitaldestruction66
ID: 6900757
Arthur_Wood

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
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 50 total points
ID: 6901085
you would place code like this:

Dim Calcluate as Currency
If IsNumeric(txtUnitPrice(Index)) And IsNumeric(txtQuantity(Index)) Then
   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(Index)) And IsNumeric(txtQuantity(Index)) Then
   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(Index As Integer, Cancel As Boolean)
    Cancel = CalcTotal(Index)
End Sub

Private Function CalcTotal(Index As Integer) As Integer
Dim Calculate As Currency

If IsNumeric(txtUnitPrice(Index)) And IsNumeric(txtQuantity(Index)) Then
   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 As Integer, Cancel As Boolean)
    Cancel = CalcTotal(Index)
End Sub


Private Sub txtDiscount_Validate(Index As Integer, Cancel As Boolean)
    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
0
 
LVL 15

Expert Comment

by:ameba
ID: 6901178
> Calculate = Val(txtUnitPrice(Index)) * Val(txtQuantity(Index)) * (1 - Val(txtDiscount(Index) / 100))

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).Text) * CDbl(txtQuantity(Index).Text) * _
        (1 - CDbl(txtDiscount(Index).Text) / 100)

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

? Val("2000")
 2000   ' OK

? Val("2.000")
 2      ' not OK
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6903801
ameba,
   Being in the States, the Locale-awareness is NOT a big issue, so it never occured to me.  Thanks for the note, though it is probably of very little significance to me, it may be to others.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Updates not working for MS Windows 7 12 182
Can we place a tooltip on the actual vb6 form 5 48
Help me. 3 60
Dinamic report to Crosstab query 9 34
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question