asked on 7/26/2021

how to round to the nearest .25 (quarter)

Hello ALL!
how to round to the nearest quarter .25
i.e.
1.20 = 1.25
1.30 = 1.50
1.65 = 1.75
1.80 = 2.00
thanks in advance

Microsoft Access

Last Comment

noci

7/27/2021

Gerwin Jansen

7/26/2021

That is a question that was answered earlier:

https://www.experts-exchange.com/questions/20447125/Rounding-to-the-nearest-quarter-hour-in-an-access-report.html

ASKER CERTIFIED SOLUTION

Daniel Pineault

7/27/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Gustav Brock

7/27/2021

Rounding to the nearest quarter would return these results:

 1.2           1.25 
 1.3           1.25 
 1.65          1.75 
 1.8           1.75

Open in new window

which can be reached using Format:

RoundedAmount = CCur(Format(Amount * 4, "0")) / 4

Open in new window

However, your example values show values rounded up. That can be done using Daniel's expression.

For all sorts of rounding, see VBA.Round.

frimy

7/27/2021

ASKER

Daniel,
your solution works beautiful,
but one problem, it adds a minus sign before the number.

a = 33.60
b = Int(-a * 4) / 4
b = -33.75

what can we do?
thanks

Gustav Brock

7/27/2021

what can we [you] do?

Use Daniel's code as originally posted ...

frimy

7/27/2021

ASKER

i can do to add Abs()
i did use the same code as posted
i just want to know what Daniel can do.

Gustav Brock

7/27/2021

I did use the same code as posted

Well, look again, this time carefully.

You cannot apply Abs, as that will prohibit the rounding of negative values.

Daniel Pineault

7/27/2021

My original code included a - at the beginning that you missed which addresses this issue.

noci

7/27/2021

Be aware of the domain used. If you multiply a value by 4 you should not overflow the values.
Also be aware of rounding behaviour... multiply by 4 device by 4 is a binary neutral operation (shift left2, shitft right 2 bits).

With non 2^n numbers you introduce more rounding due to "non-fitting" data (there is no 1/3rd bit).