Rounding number up to half hour

[crxftw]

Hello, I've been trying to look everywhere but can't seem to find a solution how I can get my numbers rounded like that for example:

2.01 to 2.24 = 2
2.25 to 2.74 = 2.5
2.75 to 2.99 = 3

I would like to that in text box control source if possible, the value I need to get rounded comes from:

[Entitlement]+Sum([TotalHolidayHours])-[txtHolidayUsed]

Which for example is 23.1 right now, but in that case I'd need it to be 23. If it would be 23.3 then it's 23.5 already.

Thanks.

 

[Mr. B]

It was not clear to me exactly where you are needing to do the calculation but here is a user defined function that will do what you want.

Public Function SetValue(ProvidedValue As Variant) As Variant
Dim varCharLoc
Dim varDecVal
'determine if there is a decimal in the value returned
If InStr(1, ProvidedValue, ".") > 0 Then
    varCharLoc = InStr(1, ProvidedValue, ".")
    varDecVal = Right(Str(ProvidedValue), varCharLoc)
    If varDecVal <= 24 Then
        SetValue = Left(ProvidedValue, varCharLoc - 1)
    ElseIf varDecVal > 24 And varDecVal < 75 Then
        SetValue = Left(ProvidedValue, varCharLoc) + 0.5
    Else
        SetValue = Left(ProvidedValue, varCharLoc - 1) + 1
    End If
Else
    SetValue = ProvidedValue
End If
End Function

Copy and paste this code into a Module. Then you can call it from a query:

RoundedVal: SetValue([ValueToBeEvaluated])

Or you can use it in a text box on a form by placing the following in the Control Srouce of the text box:

=SetValue([ValueToBeEvaluated])

Note: change the "[ValueToBeEvaluated]" to refer to the calculated value you want to round.

 

[crxftw]

Thanks for response. No the solution I was looking for is not that complex. The idea was that a number (sum of hours) can be either integer or decimal that shows half. If sum of hours is say 10.3 then it will be rounded to 10.5, if it's 10.8, it will be 11 etc. Found a simple round expression for that now:

=Round(2*([Entitlement]+Sum([TotalHolidayHours])-[txtHolidayUsed]),0)/2

 

[1992]

If you call that result in your query "Unrounded", use this:

Rounded: 0.5*INT(2*[Unrounded])+IIF((2*[Unrounded])-INT(2*[Unrounded])>0.5,0.5,0)

edit: Oh, you got is sorted. Your way is better I guess. I don't really know many functions, I just worked out how you'd do it with what I know.