Results 1 to 9 of 9
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Percentage Formula

    Hi Guy''s for the life of me, this should be simple but for some reason I can't get the correct answer, i am trying have a button to add 2.5% discount and each click increment by a further 2.5%



    If txtDisc = "" the it wants to be 2.5
    next click 5
    next click 7.5

    etc

    I am trying to take that percentage from the total ?

    Also have a button called cmdRight, this is to reverse the calculation

    For the life of me I can't get the correct answer/formula

    trying to get the txtNett to result in new value taking percentage from txtDisc
    Code:
    Private Sub cmdLeft_Click()Dim orgValue As Currency, disValue As Currency, newValue As Currency, myCalc As Currency
    
    
    orgValue = Me.txtNett
    
    
    If Me.txtDisc = "" Then
       Me.txtDisc = 2.5
    End If
    
    
    If Me.txtDisc <> "" Then
       Me.txtDisc = Me.txtDisc + 2.5
       newValue = Me.txtDisc * orgValue
       Me.txtNett = Me.txtNett / 100 * Me.txtDisc
       Me.txtVat = Me.txtNett * 0.2
       Me.txtTotal = Me.txtNett * 1.2
       Me.txtDiscount = Me.txtDisc & "% Discounted"
    End If
    
    
        
    End Sub

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    So start with default of 0, not empty.?
    Each click just add 2.5
    I would not hard code VAT, put it in a table somewhere.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    But what if Me.txtDisc is Null
    Perhaps you need:
    Code:
    If Nz(Me.txtDisc, 0) = 0 Then
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks guys, I'm just struggling with the correct formula to get the correct nett amount after deduction of txtDisc

    Me.txtNett = Me.txtNett / Me.txtDisc * 100

    I am going to test this or if a better suggestion ?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    learn to use the immediate window to test out formulae
    ?2/5*100
    40
    ?(2/5)*100
    40
    ?2/(5*100)
    0.004
    ?2*(5/100)
    0.1
    ?2-2*(5/100)
    1.9
    ?2-(2*(5/100))
    1.9

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by Bob Fitz View Post
    But what if Me.txtDisc is Null
    Perhaps you need:
    Code:
    If Nz(Me.txtDisc, 0) = 0 Then
    That is why I suggested starting with a default of 0 ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You have to take the discount against the original value each time, else you compound the discount if taken against the discounted value.

    DMTdisc-davegri-v01.zip

    Example of attached DB below. Assumes VAT of .2 is 20%.

    Click image for larger version. 

Name:	DMTdisc.png 
Views:	18 
Size:	7.3 KB 
ID:	47804

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdLeft_Click()
    If Nz(Me.txtDisc, 0) = 0 Then
        Me.txtDisc = 2.5
    Else
        Me.txtDisc = Me.txtDisc + (2.5)
    End If
        Me.txtNett = txtOrgValue - (txtOrgValue * txtDisc / 100)
        Me.txtVAT = Me.txtNett * 0.2
        Me.txtTotal = Me.txtNett + Me.txtVAT
        Me.txtDiscount = Me.txtDisc & "% Discounted"
    End Sub

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you guys, just couldn't get the formula correct

    Will study your suggestions and test, thanks agian

  9. #9
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    worked a treat guys thank you so much

Please reply to this thread with any new information or opinions.

Similar Threads

  1. percentage
    By angie in forum Forms
    Replies: 1
    Last Post: 08-13-2017, 02:11 PM
  2. Replies: 4
    Last Post: 07-16-2016, 10:52 AM
  3. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  4. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  5. Percentage
    By azhar2006 in forum Queries
    Replies: 3
    Last Post: 11-15-2014, 02:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums