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

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