Rounding up to nearest .25....need some help!!! 2

[sirkenj]

Hello again! I have the function below contained in a module in my database. It's purpose is to return a value rounded up to the nearest .25. It is currently returning #ERROR, and I am hoping some of you wiser than me might be able to offer me some suggestions as to the possible cause.

The field(s) being used for input on this are Numbers, field size double, format fixed. Any help you can offer here will be greatly appreciated.


Function RoundNearestQuarter(sNum)
Dim sDigit, iNum

sDigit = IIf(InStr(1, sNum, ".&quot > 0, CStr(Right(sNum, 2)), "0&quot
iNum = Int(sNum)

If sDigit >= &quot;10&quot; And sDigit <= &quot;25&quot; Then
sDigit = &quot;25&quot;
If sDigit >= &quot;26&quot; And sDigit <= &quot;50&quot; Then
sDigit = &quot;50&quot;
If sDigit >= &quot;51&quot; And sDigit <= &quot;75&quot; Then
sDigit = &quot;75&quot;
ElseIf sDigit >= &quot;76&quot; And sDigit <= &quot;99&quot; Then
sDigit = &quot;00&quot;
iNum = iNum + 1
End If


RoundNearestQuarter = iNum & &quot;.&quot; & sDigit
End Function

 

[GKProgrammer]

Hi srkenj,

Well possibly the problem has to do with your syntax on the If statements, try this:

Function RoundNearestQuarter(sNum)
Dim sDigit, iNum

sDigit = IIf(InStr(1, sNum, &quot;.&quot > 0, CStr(right(sNum, 2)), &quot;0&quot
iNum = Int(sNum)

If sDigit >= &quot;10&quot; And sDigit <= &quot;25&quot; Then
sDigit = &quot;25&quot;
ElseIf sDigit >= &quot;26&quot; And sDigit <= &quot;50&quot; Then
sDigit = &quot;50&quot;
ElseIf sDigit >= &quot;51&quot; And sDigit <= &quot;75&quot; Then
sDigit = &quot;75&quot;
ElseIf sDigit >= &quot;76&quot; And sDigit <= &quot;99&quot; Then
sDigit = &quot;00&quot;
iNum = iNum + 1
End If


RoundNearestQuarter = iNum & &quot;.&quot; & sDigit
End Function

Let me know if this helps.

Regards,
gkprogrammer

 

[MakeItSo]

Well first of all, I would shorten the code a bit and take care of your var types:
Dim sNum, sDigit As Double

If(InStr(1, sNum, &quot;.&quot > 0
Then sDigit = CStr(Right(sNum, 2))
else sDigit=&quot;00&quot;
End If

If sDigit >= &quot;76&quot; And sDigit <= &quot;99&quot; Then
sDigit = &quot;00&quot;
iNum = iNum + 1
Else
if (sDigit mod 25)>0 Then sDigit=((sDigit\25)+1)*25
End If

RoundNearestQuarter = CDbl(iNum)+CDbl(sDigit)/100

Does that work?
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[rdodge]

Why don't you use the following code (Watch for text wrapping below when copying and pasting):

Public Function RoundNearestInterval(ByVal sNum as Double, ByVal dInterval as Double) as Double
RoundNearestInterval = Int(sNum / dInterval + .5)/dInterval
End Function
Public Function RoundDownInterval(ByVal sNum as Double, ByVal dInterval as Double) as Double
RoundDownInterval = Int(sNum / dInterval)/dInterval
End Function
Public Function RoundUpInterval(ByVal sNum as Double, ByVal dInterval as Double) as Double
If Int(sNum / dInterval)/dInterval = sNum Then
RoundUpInterval = sNum
Else
RoundUpInterval = Round(sNum / dInterval + 1)/dInterval
End If
End Function

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[CajunCenturion]

Couple of things come to mind after reading through the thread. First of all, MakeItSo is quite correct that it's a good practice to property type all of your variables, but the line

Dim sNum, sDigit As Double

only goes have way in that sDigit will be a double, but sNum will remain a variant. To remedy this you need

Dim sNum as Double, sDigit As Double

I think rdodge has the right approach in the three functions provided, but need a couple of modifications. The final operation should not be a division by dInterval, but a multiplication by dInterval. And in the RoundUpNearestInterval, in the Else clause, the Int function should be used instead of the Round function.

Public Function RoundNearestInterval(ByVal sNum As Double, ByVal dInterval As Double) As Double
    RoundNearestInterval = Int((sNum / dInterval) + 0.5) * dInterval
End Function

Public Function RoundDownInterval(ByVal sNum As Double, ByVal dInterval As Double) As Double
    RoundDownInterval = Int(sNum / dInterval) * dInterval
End Function

Public Function RoundUpInterval(ByVal sNum As Double, ByVal dInterval As Double) As Double
    If Int(sNum / dInterval) * dInterval = sNum Then
        RoundUpInterval = sNum
    Else
        RoundUpInterval = Int((sNum / dInterval) + 1) * dInterval
    End If
End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[rdodge]

Good catches as I should have caught them in the proof reading stage.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[CajunCenturion]

I've always found it easier to see things like that in someone else's code than in my own. If I could only get what I think I'm doing out of mind and focus on what I am actually doing, my production would go way up.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MakeItSo]

Oh yeah, and it's mostly the simplest and most obvious things you don't see - and don't do!
A pink one for your paying attention, Cajun.

 

[sirkenj]

Thank you all SO much.

I ultimately am using CajunCenturions example above. I never thought to approach this from that angle, and somehow overlooked declaring the type of variables (though I took the time to tell you all what they were in my post!)

 

[CajunCenturion]

Thank you, but rdodge deserves the credit for that code.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein