
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:
115 => 15
1630 => 30
3145 => 45
4559 => 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:
> 115 => 15
> 1630 => 30
> 3145 => 45
> 4559 => 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.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

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