Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64

    Unanswered: Help with an overflow error required

    I have a database which stores stationery orders in a school for each department (e.g. Admin, Senior Management, English Dept, Science Dept, etc). From time to time we get asked to produce a report on how much stationery was ordered for each department within a given date range.
    I have set up a report which calculates this and then can be printed out or emailed to the relevant department head.
    This was all working fine until today, when my assistant phoned me up to say that when she runs the report she gets an error which reads "This expression is typed incorrectly or it is too complex....etc etc". I have verified that the query behind the report runs correctly. In order to narrow down where the problem was, I first removed the date range parameter and ran the report. This time I got a single error box simply saying the word "Overflow".
    After a bit of investigation, I have discovered that the culprit seems to be the text box on the report form which calculates the totals for each department. If I remove this box, together with its calculation, the report runs correctly. Trouble is, I need to have the calculation on there for it to be any use to the school.
    The calculation in the text box is: =SUM(([Batch Price]/[Number in Batch])*[Quantity Taken])
    As I said, this was all working perfectly until today. I have tried deleting some rows of data in case the numbers are too big, but this doesn't seem to have any effect.
    Can anyone throw any light on what's going on?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This generally means that you're entering or generating, through a calculation, a number that is too big for the Datatype that has been declared for the Field.

    • Integer Stores whole numbers from 32,768 to 32,767
    • Long Integer (Default) Stores whole numbers from 2,147,483,648 to 2,147,483,647
    • Single Stores numbers from 3.402823E38 to 1.401298E45 for negative values and from 1.401298E45 to 3.402823E38 for positive values.
    • Double Stores numbers from -1.79769313486231E308 to 4.94065645841247E324 for negative values and from 4.94065645841247E324 to 1.79769313486231E308 for positive values.

    This problem is often only incurred where you have a Report that includes Year-To-Date figures. It runs fine early in the year, until the Y-T-D figures get big enough to go over the size limit for the Field.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Thanks for the advice - much appreciated. Actually I managed to trace the error to a rogue record where the user had entered zero in every field. The system was then trying to divide by zero and this caused the overflow (a bit like when you try to divide by zero on a calculator you get the same kind of overflow error). I removed the offending record and the whole thing works perfectly again.
    Is there some code I can build in to check for a zero entry to stop this happening again?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got this fixed, but to be honest I'm confused by the reported problem! What, was the Error Number for this 'overflow?'

    Division-by-Zero is a distinct error (Error 11) in Access, and always reported as such. Error 6, Overflow, is always caused, as I said, by cramming a number that is too big into Numeric Field that is too small to hold it. There is also a Numeric Field Overflow error message (Error 3349) that occurs when you query a table that is linked to an Excel spreadsheet and Access guesses wrong about the appropriate Datatype to assign to the Field.

    When doing this kind of thing, Access looks at the first 8 rows of data in a given column of the Excel spreadsheet and guesses as to the appropriate Datatype from this information. If, for instance, the first 8 rows contained actual Numbers, and a later row contained Text (alpha characters) this type of error would be thrown.

    Is it possible that one or more of the 'zeros' in the errant Record was actually an alpha O rather than a 0 (zero)?

    At any rate, as I said, glad that you got it fixed!

    As to insuring that zeros aren't entered, you can do that with Validation Code either in the Form_BeforeUpdate event or the BeforeUpdate event for the particular Control/Textbox.

    For a given Control

    Code:
    Private Sub FieldA_BeforeUpdate(Cancel As Integer)
    If Me.FieldA = 0 Then
      MsgBox "Zero is an Invalid Value for FieldA"
      Cancel = True
    End If  
    End Sub
    For Multiple Controls/Fields
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      If Me.FieldA = 0 Then
        MsgBox "Zero is an Invalid Value for FieldA"
        Cancel = True
        FieldA.SetFocus
        Exit Sub
      End If
      If Me.FieldB = 0 Then
        MsgBox "Zero is an Invalid Value for FieldB"
        Cancel = True
        FieldB.SetFocus
        Exit Sub
      End If
      End Sub
    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Strangely, I didn't get an error number. The message box simply had the word "Overflow" in it, but no number.
    It's very possible that a letter O was entered instead of a zero - I can't check this because I deleted the record. To be honest, I just trawled through the records by eye to see if there was anything unusual, and I spotted this one with what appeared to be a zero in one of the fields. I deleted the record and everything worked again, so I assumed it was trying to divide by zero in the calculation.
    Many thanks for the code - I'll put that in later today and then we should be ok. Will that trap an input of the letter O also?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    design your columns using the appropriate datatype
    if its numeric use a numeric datatype
    if its a date or time use a datetime datatype
    and so on
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Obvious really!
    Thanks!

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To trap for Zeros and alpha Os you'd have to use
    Code:
    If Me.FieldA = O or Me.FieldA = 0 Then
    Then
    Actually, eyeballing Records is sometimes the only thing that works! We frequently hear of entire Databases becoming corrupt, and individual Controls can become corrupt, also, but so can individual Records! The characters in corrupted Controls typically, but not always show up as 'Chinese' characters, which is to say they often display what look like Text using non-standard character sets. Often deleting these Records will solve the entire problem.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    Quote Originally Posted by Missinglinq View Post
    To trap for Zeros and alpha Os you'd have to use
    Code:
    If Me.FieldA = O or Me.FieldA = 0 Then
    Then
    Actually, eyeballing Records is sometimes the only thing that works! We frequently hear of entire Databases becoming corrupt, and individual Controls can become corrupt, also, but so can individual Records! The characters in corrupted Controls typically, but not always show up as 'Chinese' characters, which is to say they often display what look like Text using non-standard character sets. Often deleting these Records will solve the entire problem.

    Linq ;0)>
    Many thanks for this.
    I was lucky - there were only about 795 records in the database so it was not too difficult to spot the rogue one.

Posting Permissions

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