Results 1 to 6 of 6
  1. #1
    joshynaresh is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2013
    Posts
    131

    division on vba with formating

    i have 4 fields. 1. Principle 2. IntRate 3. days 4. IntPerDay. I have to find interest per day and total interest

    Me.IntPerDay.Value = Nz([Principle], 0) * Nz([IntRate], 0) / 36500 for per day interest

    and

    Me.Interest_Debit.Value = [Days] * [IntPerDay] for total interest

    suppose i have principle amount 123.32 and interest rate 12.33 and day 5

    then

    i got

    Interest per day = 0.0416 and
    total interest = 0.2082

    but i want

    Interest per day = 0.04 and
    total interest = 0.20


    can it is possible.
    Thank you in advance

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,505
    Wrap your expression in the Round Function


    http://www.techonthenet.com/access/f...eric/round.php

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    total interest = 0.2082
    ...
    total interest = 0.20
    Do you really want .2082 to round down to .20, or should it round to .21?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    If this is real money, you'd be legally safer by keeping the decimals in the daily rate and rounding or truncating the end total. When I did this kind of thing, the standard was to truncate to .20 and retain the .0082 in unpaid interest differential in a different field, to add to the result next time.

  5. #5
    joshynaresh is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2013
    Posts
    131
    i want round in daily interest amount Rs.0.04 and then multiply with day and result should come Rs. 0.20

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I see, so it looks like you want to apply the rounding to the factor before the multiplication.
    Just apply the ROUND function to that part of your formula before multiplying (see Alan's post/reference on rounding), i.e.
    Code:
    Me.Interest_Debit.Value = [Days] * Round([IntPerDay],2)
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Similar Threads

  1. #Num! error division by 0
    By smc678 in forum Access
    Replies: 5
    Last Post: 10-14-2013, 11:41 AM
  2. Replies: 3
    Last Post: 07-31-2013, 08:46 PM
  3. division by 0
    By kwooten in forum Queries
    Replies: 5
    Last Post: 03-19-2012, 04:37 PM
  4. Conditional formating!
    By karanvemuri in forum Access
    Replies: 3
    Last Post: 10-29-2011, 03:34 PM
  5. Division by zero error on a query
    By thorwood in forum Access
    Replies: 3
    Last Post: 05-19-2011, 07:20 AM

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