jamiei
asked on
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
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
ASKER
Yes, but for reasons too long to explain the round function is not going to work for me.
ASKER
The round function is not consistent in that it doesn't consistenly round up, that is why I cannot use it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Private Sub Command1_Click()
MsgBox FormatNumber(-2193439.275,
End Sub
MsgBox FormatNumber(-2193439.275,
End Sub
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),
If CInt(Right(CStr(dblNumber)
RoundUp = dblNumber
Else
RoundUp = dblNumber
End If
End Function
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),
If CInt(Right(CStr(dblNumber)
RoundUp = dblNumber
Else
RoundUp = dblNumber
End If
End Function
are you properly handling both negative and positive numbers in your function?
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.
ASKER
Unfortunately the function from muzzy2003 did not fix the problem. I am trying the FormatNumber right now and we'll see if that works.
>are you properly handling both negative and positive numbers in your function?
sorry muzzy2003
no it was directed a jamie
sorry muzzy2003
no it was directed a jamie
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.
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
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
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),
RoundUp = dblNumber
Else
RoundUp = dblNumber
End If
End Function
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),
RoundUp = dblNumber
Else
RoundUp = dblNumber
End If
End Function
Muzzy
If your routine gets given a four decimal number, say 429.2715, what's it going to return?
If your routine gets given a four decimal number, say 429.2715, what's it going to return?
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".
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.
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?
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.
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.
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),
RoundUp = dblFinal
Else
RoundUp = dblNumber
End If
End 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),
RoundUp = dblFinal
Else
RoundUp = dblNumber
End If
End Function
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!
ASKER
This worked great, thank you so much to everyone, especially muzzy!!!!
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),
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
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),
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
ASKER
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' ;)
Sorry. That's unforgiveable. I know a couple of male Jamies ...
Incidentally, which rounding method did you mean?
Incidentally, which rounding method did you mean?
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?