Printable View
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
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
I'm pretty sure 'MOD' is the "modulus" operator in Access. Ex: val1 MOD val2.
