Results 1 to 7 of 7
  1. #1
    Join Date
    May 2008
    Posts
    48

    Unhappy Unanswered: Problem with Select Case

    I am confused how to get the figure of (Total Value of Order) derived after multiplying the (Unit Price) and (Quantity Ordered ) based on following discount pattern:

    Discount 10%
    Discount 5%
    Discount 3% and so on.

    I have tried to use following code to get the desired figure, in the event of AFTER UPDATE (Quantity) but an error occured

    Private Sub Quantity_AfterUpdate()
    Dim Discount As Integer
    'Me.txtTotal = (Me!Quantity * Me!UnitPrice)
    Select Case Discount = 10
    Me.txtTotal = ((Me!Quantity * Me!UnitPrice) * 0.9)
    Case Discount = 5
    Me.txtTotal = ((Me!Quantity * Me!UnitPrice) * 0.9)
    Case Else
    Me.txtTotal = (Me!Quantity * Me!UnitPrice)
    End Select
    End Sub


    Any help in this regard is solicited.

  2. #2
    Join Date
    May 2008
    Posts
    48
    Following code may please be corrected as

    Private Sub Quantity_AfterUpdate()
    Dim Discount As Integer
    Select Case Discount = 10
    Me.txtTotal = ((Me!Quantity * Me!UnitPrice) * 0.9)
    Case Discount = 5
    Me.txtTotal = ((Me!Quantity * Me!UnitPrice) * 0.95)
    Case Else
    Me.txtTotal = (Me!Quantity * Me!UnitPrice)
    End Select
    End Sub

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you would be better off storing discount rules as a separate table, storing a discount code in th eproduct table. that give you the flexibility to apply whatever discount is required. it has the added advantage that control of the discount calcualtion is in the hands of the the user.. its requires no further action from development to support the business rules in this area it also allows the users to make whatever changes they like when they like.

    doing the calculation the way you are is OK if you know your user is never ever going to change, which is something I doubt any developer or user can guarantee. I would advise you place the discount calculation in another event rather than afterupdate. possibly before update, or even onchange, onlostfocue,beforeupdate events of the quantity column/control

    Code:
    private function CalcLinePrice(Qty as integer, UnitPrice as double) as double
    dim DiscountRate as double
    Select Case Qty
    case  >= 10
      DiscountRate = 0.9
    Case >= 5
      DiscountRate = 0.95
    Case Else
      DiscountRate=1
    end select
    CalcLinePrice = Me!Quantity * UnitPrice * DiscountRate
    end function
    Last edited by healdem; 09-09-09 at 03:40.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2008
    Posts
    48
    Dear Healdem

    Thanks for your reply. However the same does not serve my purpose. I do not want to check the quantity ordered. as you mentioned in code. I want to check the discount % (already entered in the table) and based on Quantity ordered & Unit Price (already entered in the table), calculate & store the ordered value.

    From the above, it is clear that I want to get the figure of (Quantity Ordered) multiplied by (Unit Price) calculated & stored in a seperate field based on discount pattern as with following If and Endif conditions.

    If Discount = 10
    Qty Ordered * Unit Price * 0.9
    If Discount = 5
    Qty Ordered * Unit Price * 0.95
    Endif
    Endif

    Please note that Discount percentage, Unit Price and Quantity Ordered are in one table.

    Hope, now you are clear about my requirment. For any further clarification/doubt, please revert back.

    Thanks,

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Case statements would be unnecessary in this circumstance as surely the formula is always the same?

    (1-(YourDiscountField/100))*(YourQuantity)*(YourUnitPrice)

    Just use an unbound control on the (form?)...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by sanjaylml
    Dear Healdem

    Thanks for your reply. However the same does not serve my purpose. I do not want to check the quantity ordered. as you mentioned in code. I want to check the discount % (already entered in the table) and based on Quantity ordered & Unit Price (already entered in the table), calculate & store the ordered value.

    From the above, it is clear that I want to get the figure of (Quantity Ordered) multiplied by (Unit Price) calculated & stored in a separate field based on discount pattern as with following If and Endif conditions.

    If Discount = 10
    Qty Ordered * Unit Price * 0.9
    If Discount = 5
    Qty Ordered * Unit Price * 0.95
    Endif
    Endif

    Please note that Discount percentage, Unit Price and Quantity Ordered are in one table.

    Hope, now you are clear about my requirment. For any further clarification/doubt, please revert back.

    Thanks,

    I'm sure its just language but in all honesty I have better things to do than revisit this. ultimately you got a solution for the problem you outlined, if that solution doesn't meet your requirements then probably you should have better defined your requirements in your first post.

    as I see it you have a function which calculates the line price. you have a varying discount rate based on quantity sold. this function can be called rom any appropriate event.

    ultimately I don't care if my suggestion is used by you. as far as I can understand what you want you have got a solution

    if everything is in the table then surely you just apply a suitable expression on the SQL. the advantage being that you don't need a function.. its done in SQL

    eg
    Code:
    select my,column,list, UnitPrice*Qty*DiscountRate as LinePrice from MyTable
    if you store the discount rate as an integer ie 10 = 10% discount then
    Code:
    select my,column,list, UnitPrice*Qty*(100-DiscountRate)/100 as LinePrice from MyTable
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "you have a varying discount rate based on quantity sold"

    >I don't think this is the case - I think hes just applying a discount %???
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •