Overflow error on Mod Function

I am using Access 2002 and I wrote a function that will round numbers up instead of using 'Bankers rounding'.  When I run the code it works for a lot of the numbers that are smaller but when it gets to a large number I get an 'Overflow Error' and when I debug it takes me to the line of code where I'm calling the MOd function.  I have tried declaring the variable as variant, string, double, and nothing and none of these solve the problem.  Any suggestions on what to do to avoid this overflow error.  I will paste the code below and know that dblNumber that is being passed is -2193439.275 and my goal is for it to round to -2193439.28.

Public Function RoundUp(dblNumber As Double) As Double
Dim pos As Integer
Dim intEval As Integer
Dim dblFinal As String
Dim dblRem As String
Dim dblInt As Double

dblFinal = dblNumber * 1000
dblRem = CDbl(dblFinal) Mod 10
dblInt = Int(dblFinal) - dblRem
If dblRem >= 5 Then
    dblFinal = (dblInt + 10) / 1000
Else
    dblFinal = dblInt / 1000
End If

RoundUp = dblFinal

End Function
jamieiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RogueSolutionsCommented:

The ROUND function itself will do what you want I think.  You can specify how many decimal places.

So ...

ROUND( -2193439.275, 2) gives -2193439.28

Does that help?
0
jamieiAuthor Commented:
Yes, but for reasons too long to explain the round function is not going to work for me.
0
jamieiAuthor Commented:
The round function is not consistent in that it doesn't consistenly round up, that is why I cannot use it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RogueSolutionsCommented:

OK.  Am seeing if I can find a way around MODs limit
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vinnyd79Commented:
Private Sub Command1_Click()
MsgBox FormatNumber(-2193439.275, 2)
End Sub
0
muzzy2003Commented:
Your method can't handle numbers this big. The Mod function takes a Long value, so despite your attempt to force it to another data type (Dbl in your posted code, but you say you've tried lots), the value will be implicitly converted to a Long first, and the value is outside the range of values for this. Round doesn't always go the way you want due to the way floating point numbers are stored.

Does this fit the bill?

Public Function RoundUp(dblNumber As Double) As Double

Dim dblFinal As String

If Len(CStr(dblNumber)) - InStr(CStr(dblNumber), ".") = 3 Then

    dblFinal = CDbl(Left(CStr(dblNumber), Len(CStr(dblNumber)) - 1))
    If CInt(Right(CStr(dblNumber), 1)) >= 5 Then dblFinal = dblFinal + Sgn(dblNumber) * 0.01
    RoundUp = dblNumber

Else

    RoundUp = dblNumber

End If

End Function
0
LowfatspreadCommented:
are you properly handling both  negative and positive numbers in your function?

0
muzzy2003Commented:
If that was directed at me, then: yes - since I'm only looking at the right hand end of the string in the logic, and the key was to round 0.005 AWAY from zero. The Sgn(dblNumber) takes care of that for me.
0
jamieiAuthor Commented:
Unfortunately the function from muzzy2003 did not fix the problem.  I am trying the FormatNumber right now and we'll see if that works.
0
LowfatspreadCommented:
>are you properly handling both  negative and positive numbers in your function?


 sorry muzzy2003

no it was directed a jamie
0
muzzy2003Commented:
Can you tell me how mine didn't? I may have misunderstood, and in any event I'd like to try to fix it. Ta.
0
RogueSolutionsCommented:
OK.  How about this ...

Public Function RoundUp(dblNumber As Double) As Double

    Dim dblAddOn    As Double
   
    dblAddOn = 0.01
    If Int(Abs(dblNumber) * 100) = Abs(dblNumber) * 100 Then dblAddOn = 0
   
    If dblNumber > 0 Then
        RoundUp = (Int(dblNumber * 100) / 100) + dblAddOn
    Else
        RoundUp = -1 * ((Int(Abs(dblNumber) * 100) / 100) + dblAddOn)
    End If
   
End Function

It will round up regardless of the 1000th decimal (the 5 in your example) UNLESS the 1000th place decimal is a 0 (or missing)

RoundUp(-2193439.271) becomes -2193439.28
RoundUp(-2193439.278) becomes -2193439.28
RoundUp(2193439.278) becomes 2193439.28
RoundUp(2193439.272) becomes 2193439.28

and

RoundUp(2193439.27) becomes 2193439.27
RoundUp(-2193439.27) becomes -2193439.27

0
muzzy2003Commented:
If you want the rounding RogueSolutions described, you can modify my function as:

Public Function RoundUp(dblNumber As Double) As Double

Dim dblFinal As String

If Len(CStr(dblNumber)) - InStr(CStr(dblNumber), ".") = 3 Then

    dblFinal = CDbl(Left(CStr(dblNumber), Len(CStr(dblNumber)) - 1)) + Sgn(dblNumber) * 0.01
    RoundUp = dblNumber

Else

    RoundUp = dblNumber

End If

End Function
0
RogueSolutionsCommented:
Muzzy

If your routine gets given a four decimal number, say 429.2715, what's it going to return?

0
muzzy2003Commented:
Well, I had thought from the question that this wouldn't be the case. If it is, however, then just change the "= 3" in my function to ">= 3".
0
RogueSolutionsCommented:

Sorry, it doesn't like that either.  I was tempted by the 'text' route at first but figured I couldn't be sure I wasn't rounding before trying to test so played with the INT function instead.
0
muzzy2003Commented:
My first function wouldn't like that. My second, with ">= 3", if it is the rounding wanted, should do. Doesn't it? Got an example?
0
RogueSolutionsCommented:
I've only tried the second one (had to rename it to stop a clash, hence a 2)

Example was RoundUp2(2193439.2715) and it gave it right back to me.

I think the problem is with this bit "Len(CStr(dblNumber)) - 1" - it's clearly hacking the end number off and therefore thrown by the extra digit.

You could do Format(dblNumber,"0.000") instead of CStr but not sure if that will mess the rounding up between the 3rd and 4th digits.
0
muzzy2003Commented:
OK. I'm at home now and concentrating. RoundUp = dblFinal not RoundUp = dblNumber would have helped as well! Try this function:

Public Function RoundUp(dblNumber As Double) As Double

Dim dblFinal As String
Dim intDecimals As Integer

intDecimals = Len(CStr(dblNumber)) - InStr(CStr(dblNumber), ".")

If intDecimals >= 3 Then

    dblFinal = CDbl(Left(CStr(dblNumber), Len(CStr(dblNumber)) - (intDecimals - 2))) + Sgn(dblNumber) * 0.01
    RoundUp = dblFinal

Else

    RoundUp = dblNumber

End If

End Function
0
RogueSolutionsCommented:

Yep, that one works OK.  Needs a minor tweak to handle an Integer (no decimal at all) but otherwise does what mine does and hopefully that's what Jamie is after!
0
jamieiAuthor Commented:
This worked great, thank you so much to everyone, especially muzzy!!!!
0
muzzy2003Commented:
Yes, we're assuming we've now identified the rounding he wants. If he actually wanted the rounding I went for first time, then this works:

Public Function RoundUp(dblNumber As Double) As Double

Dim dblFinal As String
Dim intDecimals As Integer

intDecimals = Len(CStr(dblNumber)) - InStr(CStr(dblNumber), ".")

If intDecimals >= 3 Then

    dblFinal = CDbl(Left(CStr(dblNumber), Len(CStr(dblNumber)) - (intDecimals - 2)))
    If CInt(Mid(CStr(dblNumber), Len(CStr(dblNumber)) - (intDecimals - 3), 1)) >= 5 Then dblFinal = dblFinal + Sgn(dblNumber) * 0.01
    RoundUp = dblFinal

Else

    RoundUp = dblNumber

End If

End Function
0
jamieiAuthor Commented:
The only thing I had to change here is that I had to check that there was in fact a "." in the number before it went through the intDecimals line.  If there was no "." I just pass 0 to intDecimals.  Thanks again!  And 'he' is a 'she'  ;)
0
muzzy2003Commented:
Sorry. That's unforgiveable. I know a couple of male Jamies ...

Incidentally, which rounding method did you mean?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.