Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Overflow error on Mod Function

Posted on 2004-11-24
24
Medium Priority
?
968 Views
Last Modified: 2012-06-21
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
Comment
Question by:jamiei
  • 9
  • 7
  • 5
  • +2
24 Comments
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12667811

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

by:jamiei
ID: 12667828
Yes, but for reasons too long to explain the round function is not going to work for me.
0
 

Author Comment

by:jamiei
ID: 12667860
The round function is not consistent in that it doesn't consistenly round up, that is why I cannot use it.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 5

Accepted Solution

by:
RogueSolutions earned 1500 total points
ID: 12667909

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

Expert Comment

by:vinnyd79
ID: 12667928
Private Sub Command1_Click()
MsgBox FormatNumber(-2193439.275, 2)
End Sub
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12667936
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

by:Lowfatspread
ID: 12667943
are you properly handling both  negative and positive numbers in your function?

0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12668169
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

by:jamiei
ID: 12668186
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

by:Lowfatspread
ID: 12668192
>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

by:muzzy2003
ID: 12668269
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

by:RogueSolutions
ID: 12668426
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12668520
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

by:RogueSolutions
ID: 12668551
Muzzy

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

0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12668572
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

by:RogueSolutions
ID: 12668610

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

by:muzzy2003
ID: 12668637
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

by:RogueSolutions
ID: 12668678
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

by:muzzy2003
ID: 12668859
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

by:RogueSolutions
ID: 12669020

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

by:jamiei
ID: 12669049
This worked great, thank you so much to everyone, especially muzzy!!!!
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12669058
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

by:jamiei
ID: 12669427
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

by:muzzy2003
ID: 12669458
Sorry. That's unforgiveable. I know a couple of male Jamies ...

Incidentally, which rounding method did you mean?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

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

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

Join & Ask a Question