Solved

# Overflow error on Mod Function

Posted on 2004-11-24
959 Views
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
0
Question by:jamiei

LVL 5

Expert Comment

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

Author Comment

Yes, but for reasons too long to explain the round function is not going to work for me.
0

Author Comment

The round function is not consistent in that it doesn't consistenly round up, that is why I cannot use it.
0

LVL 5

Accepted Solution

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

LVL 28

Expert Comment

Private Sub Command1_Click()
MsgBox FormatNumber(-2193439.275, 2)
End Sub
0

LVL 16

Expert Comment

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

LVL 50

Expert Comment

are you properly handling both  negative and positive numbers in your function?

0

LVL 16

Expert Comment

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

Author Comment

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

LVL 50

Expert Comment

>are you properly handling both  negative and positive numbers in your function?

sorry muzzy2003

no it was directed a jamie
0

LVL 16

Expert Comment

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

LVL 5

Expert Comment

Public Function RoundUp(dblNumber As Double) As Double

Dim dblAddOn    As Double

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

LVL 16

Expert Comment

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

LVL 5

Expert Comment

Muzzy

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

0

LVL 16

Expert Comment

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

LVL 5

Expert Comment

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

LVL 16

Expert Comment

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

LVL 5

Expert Comment

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

LVL 16

Expert Comment

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

LVL 5

Expert Comment

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

Author Comment

This worked great, thank you so much to everyone, especially muzzy!!!!
0

LVL 16

Expert Comment

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

Author Comment

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

LVL 16

Expert Comment

Sorry. That's unforgiveable. I know a couple of male Jamies ...

Incidentally, which rounding method did you mean?
0

## Join & Write a Comment Already a member? Login.

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no oâ€¦
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games. Â  The game allows for a choice of who goes first and keeps track of the number of wins forâ€¦
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦

#### 755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!