Round to nearest quarter

[TraciAnnNeedsHelp]

I couldn't find this on a previous post anywhere. The Round function rounds
to the specified decimal. I need to round to the nearest quarter (.25)

e.g. If the value is 2.37 then round to 2.25, If the value is 2.38 then
round to 2.5

Thanks!
TraciAnn

 

[Birk Binnard]

You will have to do that with code. Here's the general idea:

1. Save original value
2. Get the integer part of original value and subtract it leaving only
decimal part
3. If decimal part is < 0.25 then new decimal part is 0
4. If decimal part is < 0.5 then new decimal part is 0.25
5. If decimal part is < 0.75 then new decimal part is 0.5
6. If decimal part is < 0.99 then new decimal part is 0.75
7. Add new decimal part to integer part

Hope this helps.

 

[KARL DEWEY]

Try this --
To the quarter: Int(4*[Cost]+0.5)/4

 

[Jack Cannon]

TraciAnn,

Here is another solution that accommodates negative amounts.

Public Function GetCurQtrs(ByRef curAmount As Currency) As Currency
On Error GoTo Err_GetCurQtrs

Dim curAbsolute As Currency
Dim curDollars As Currency
Dim curCents As Currency

curAbsolute = Abs(curAmount)
curDollars = Int(curAbsolute)
curCents = curAbsolute - curDollars
Select Case curAbsolute - curDollars
Case Is < 0.125
curCents = 0
Case Is < 0.375
curCents = 0.25
Case Is < 0.625
curCents = 0.5
Case Is < 0.875
curCents = 0.75
Case Else
curCents = 1
End Select
GetCurQtrs = Sgn(curAmount) * (curDollars + curCents)

Exit_GetCurQtrs:
Exit Function

Err_GetCurQtrs:
MsgBox "Function is GetCurQtrs" & vbNewLine & _
Err.Number & vbNewLine & Err.Description
Resume Exit_GetCurQtrs

End Function

Jack Cannon

 

[Michael Gramelspacher]

I couldn't find this on a previous post anywhere. The Round function rounds
to the specified decimal. I need to round to the nearest quarter (.25)

e.g. If the value is 2.37 then round to 2.25, If the value is 2.38 then
round to 2.5

Thanks!
TraciAnn

See if this works:

SELECT my_number,
IIf(my_number+0.125>=(Int(my_number/0.25)*0.25)+0.25,(Int(my_number/0.25)*0.25)+0.25,Int(my_number/0.25)*0.25)
AS Rounded
FROM My_Table;

 

[Michael Gramelspacher]

Try this --
To the quarter: Int(4*[Cost]+0.5)/4

Very good! Same results as my response, but much simplier.