Trying to round in quarter increments "code problem"

[sdimaggio]

I'm new to access and trying to round to the nearest quarter. ie. 1.4 = 1.5, 1.1=1.25, 1.6=1.75, 1.9=2.0

I wrote the following module but something is wrong?
Option Compare Database

Function qtrround(interval)

Dim wholenumber As Long, fracnumber As Long

wholenumber = Int(interval)
fracnumber = (interval) - wholenumber
If fracnumber > (7 / 60) Then fracnumber = (0.25)
If fracnumber > (22 / 60) Then fracnumber = (0.5)
If fracnumber > (37 / 60) Then fracnumber = (0.75)
If fracnumber > (52 / 60) Then fracnumber = (1)

end if

qtrround = (wholenumber + fracnumber)

End Function

I want to be able to type ControlSource: =qtrround([interval]) and get the number to round to the nearest qtr.

Where am I going wrong here?

Thanks

 

[jebry]

Hi!

Try this:

Function qtrround(interval)

Dim wholenumber As Long, fracnumber As Long

wholenumber = Int(interval)
fracnumber = (interval) - wholenumber
If fracnumber > (52 / 60) Then fracnumber = (1)
ElseIf fracnumber > (37 / 60) Then fracnumber = (0.75)
ElseIf fracnumber > (22 / 60) Then fracnumber = (0.50)
ElseIf fracnumber > (7 / 60) Then fracnumber = (0.25)
Else fracnumber = 0
end if

qtrround = (wholenumber + fracnumber)

End Function

When you went from the lowest to the highest the function automatically set fracnumber to 0.25 as long as the number was > 7/60. I used the elseifs to allow the last else which will set the fracnumber to zero if it is .1 or less. If you don't want that just leave it out. The elseifs are also important because the program will still change the fracnumber to 0.25 without it.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Craig0201]

Eek......Nasty code.....

Rounding to the various DP is done by....

Function Round(YourNumber As Double,NumberOfDPs as Integer) As Double

Round = (CLng((YourNumber * (10^NumberOfDPs))+0.5))/(10^NumberOfDPs)

End Function

So.....

Rounding to the nearest quarter is done by:

Round(4 * 'Input your number here,0)/4

No hard coding and reusable code........

Craig

 

[sdimaggio]

Craig,

Thanks for keeping it simple.
Jebry Thanks Too!

Steve

 

[jebry]

Hi Craig!

I played with the Round function as well, but the criteria given was that anything over 7/60 was to round up to 0.25 and 7/60 is .117 approximately and the Round function doesn't start rounding to 0.25 until .126 so I went with the criteria given. If the criteria is not set in stone then certainly the Round function will give more readable code and should be used. Thanks for bringing it out because I should have given it as an alternative even if it doesn't fit the criteria.

Jeff Bridgham
bridgham@purdue.edu

 

[MichaelRed]

My Contribution.

Public Function BasRound2Val(Number As Double, Multiple As Single) As Double

    'Michael Red.    2/16/2002.  To Round UP to the Multiple

    'Usage: ? BasRound2Val(1.001, 0.25)
            ' 1.25

    'Set up variables
    Dim dblDivided As Double
    Dim intDivided As Integer


    dblDivided = Number / Multiple      ''Divide

    intDivided = Int(dblDivided)        'Integerise

    intDivided = Round(dblDivided, 0)   'Round

    'Return result, returning to nearest multiple
    If (intDivided = dblDivided) Then
        BasRound2Val = (intDivided * Multiple)
     Else
        BasRound2Val = (intDivided * Multiple) + Multiple
    End If

End Function

maggioked for the function to ROUND UP the input (to 0.25). I assume his hard coded figures are just the examples or some arbitrary break points. The function here will generaly ROUND Up the first arg to a multiple of the second, however when the first is ~~= a multiple of the second, it is not rounded up.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over