# Thread: Help with an overflow error required

1. Registered User
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. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
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.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values.
• Double Stores numbers from -1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 4.94065645841247E–324 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)>

3. Registered User
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. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
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)>

5. Registered User
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

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

8. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
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)>

9. Registered User
Join Date
Mar 2007
Location
Bedfordshire, UK
Posts
64
Originally Posted by Missinglinq
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
•