Trimming Decimal Value and Rounding in MS Access

[Zafar]

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

[Greg Nash]

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

[cobaia]

I'm pretty sure 'MOD' is the "modulus" operator in Access. Ex: val1 MOD val2.