Results 1 to 8 of 8

Thread: Overflow Error

  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Overflow Error

    Hi,

    How can I avoid overflow error # 6 thru VBA code?

    I tried following way but does not work..

    If Err.Number = 6 then
    AvgPrice = 0

    'Or sending control to a sub routing

    End if

    What would be the solution?

    Actually in VBA, AvgProce being calculated as follows:

    AvgPrice = TotValue / CurrStock

    It works fine when TotValue or CurreStock (one of them) is holding some value. But if both are them are 0 then producing said error.

    Any idea?

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Code:
    private sub doCalc()
        on error goto err_doCalc
        result = numerator / denominator
    exit_doCalc:
        exit sub
    err_doCalc:
        select case err.number
            case 6
                msgbox "Overflow", vbexclamation, "Error"
            case 11
                msgbox "Divide by zero", vbexclamation, "Error"
            case else
                msgbox "Some other error happened", vbexclamation, "Error"
            end select
        resume exit_doCalc
    end sub
    works in any MDE but for it to work in MDB
    open VB-editor then menu:
    Tools/Options/General/ErrorTrapping
    this option should be set to BreakOnUnhandledErrors

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Izy's code will give you custom messages for the errors, but I think you really need to address the underlying problem so that you don't get the error to begin with! You need to test to see if CurrStock equals zero, in which case you don't run your calculation, and simply assign a value of zero to AvgPrice, if that's what you want to do.

    Private Sub doCalc()
    If CurrStock = 0 Then
    AvgPrice = 0
    Else
    AvgPrice = TotValue / CurrStock
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    Hi MissingLinq,
    I believe you will get Error 11 (divide by zero) in your situation. I think the OP's problem is that the AvgPrice variable/field is too small. Probably set as an Integer (Long/Short) instead of Double.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I always think of the field being too small, also, when overflow occurs, which is the cause in 99% of the cases that I've seen/heard of, but in point of fact, Ashfaque is partially corrrect in stating that

    It works fine when TotValue or CurrStock (one of them) is holding some value. But if both are them are 0 then producing said error.
    3/0 yields "Error 11 Division by Zero"

    but 0/0 does, indeed, yield "Error 6 Overflow"

    The other part of the OP's statement
    It works fine when TotValue or CurrStock (one of them) is holding some value. But if both are them are 0 then producing said error.
    is slightly off, as TotValue can hold a value but if CurrStock is zero it will return Error 11. Hence my code that checks to see if CurrStock is zero and if it is bypasses the calculation, placing zero as the value of AvgPrice, which was the OP's preference in this case.

    0/3 will yield zero, and since this is what the OP wants assigned to AvgPrice if either TotValue or CurrStock is zero, nothing needs to be done to check for a value other than zero in TotValue.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I think Missinglinq is code may be ok. It worked when both fields are 0. But yet I did not tested its action if one of them holding value. I am waiting for that kind of transaction as I m working on live data. I will come back when that incident occurs

    Thanks for the help.

    With kind regards,
    Ashfaque

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You should understand that you're taking a tremendous risk "working on live data!" Entering beaucoup amounts of data in a database that hasn't been thouroughly tested could result in the loss of said data, plus the wasting of the users time and energy!

    Having said that, you should at the very least make a copy of your database that you can use for testing out new procedures in. I keep a separate folder for holding development copies of all my databases. You really don't want to sit around and wait until you just happen to have data where TotValue and CurrStock are both zero!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks for the guidelines again Missling. In fact I took a back up before including your code. The entries will not be exceeding than 10 per day so even it generates wrong calculation, I would be able to update it manually.

    But I will enter some dummy data this evening to check if it works with all criiteria and let you know.

    Thanks again.
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •