Trimming Decimal Value and Rounding in MS Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Trimming Decimal Value and Rounding in MS Access

Hybrid View

  1. #1
    Zafar Guest

    Trimming Decimal Value and Rounding in MS Access


    Hi,
    I have table with 2 fields
    f1 char(20)
    f2 number(5,2)

    The f2 column values are:
    4.10
    5.23
    7.32

    I need to round the decimal part like this; if the decimal part is in range:
    1-15 => 15
    16-30 => 30
    31-45 => 45
    45-59 => decimal part should be 0 and 1 should be added into integer
    column.

    Example:
    1.09 => 1.15
    1.23 => 1.30
    1.36 => 1.45
    1.48 => 2.00

    Can anyone help me out, how can this be done in MS Access.
    Thanks
    Zafar

  2. #2
    Greg Nash Guest

    Re: Trimming Decimal Value and Rounding in MS Access

    Hi,

    Here's three approaches, one mathematical, one using time functions, and one
    logically derived.

    Mathematical:
    I think '%' is the "modulus" operator in Access. If not, change the following
    accordingly..

    integer(f2 + 0.54) + (integer((double(f2)-integer(f2) + 0.141) * 5 / 3 * 4) % 4)
    / 4 * 0.15
    which simplifies to:
    integer(f2 + 0.54) + (integer((double(f2)-integer(f2) + 0.141) * 20 / 3) % 4) /
    0.0375

    (The first part takes care of rounding up to whole amounts. The double increases
    mathematical resolution for the fractions. The 0.141 takes care of tiny rounding
    problems. The 5/3 converts to a fraction of a whole, the * 4, int, /4 rounds
    down. The 0.15 converts back to multiples of 0.15)
    ---------------------------------
    Time:
    Whether your requirement is for times or not, the time function appears to suit
    your purposes (subject to handling of midnight).
    I can't remember the syntax well enough, but basically..
    - Convert to a datetime type
    - Add 14 minutes
    - Take the minute part, divide it by 15, convert to integer, multiply by 15, and
    make that the minute part
    - or, take the minute part, mod it by 15, and subtract that many minutes from the
    time

    ---------------------------------
    Logic:

    Use a CASE statement on the minute part.

    ---------------------------------

    Hope that helps,
    --Greg


    Zafar wrote:

    > Hi,
    > I have table with 2 fields
    > f1 char(20)
    > f2 number(5,2)
    >
    > The f2 column values are:
    > 4.10
    > 5.23
    > 7.32
    >
    > I need to round the decimal part like this; if the decimal part is in range:
    > 1-15 => 15
    > 16-30 => 30
    > 31-45 => 45
    > 45-59 => decimal part should be 0 and 1 should be added into integer
    > column.
    >
    > Example:
    > 1.09 => 1.15
    > 1.23 => 1.30
    > 1.36 => 1.45
    > 1.48 => 2.00
    >
    > Can anyone help me out, how can this be done in MS Access.
    > Thanks
    > Zafar



  3. #3
    Join Date
    Sep 2006
    Posts
    1
    I'm pretty sure 'MOD' is the "modulus" operator in Access. Ex: val1 MOD val2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center