Numeric Feild Overflow

[toddbingham]

Getting this error while running an append query. Will get it one time and not the next. Why would this be doing this, there are not any feilds that are to small to handle any numeric data. AS a matter of fact, there is only one feild that has a numeric value and it is only 12 digits in size and the feild is set to Long Integer. Can anyone help me here, this is driving me crazy. I need to make it go away.

Thanks.

 

[dgm]

Integer -32,768 to 32,767
Long (long integer) -2,147,483,648 to 2,147,483,647

Have u tried using double instead of long?

 

[toddbingham]

I have tries using every format option. I did a search in the forums, seems other people have had the same problem. This happens intermitantly, that is what I dot understand.

 

[RayH]

We import a delimited text file via a macro each morning in order to generate a report and had a similar problem until recently. The macro would fail each time.
So, I set the database to compact on close and now the problem has 'gone away'. I don't know the cause unfortunately.

 

[dgm]

I have tries using every format option. I did a search in the forums, seems other people have had the same problem. This happens intermitantly, that is what I dot understand.

If your number is 12 digits long and your Field size is set to long integer, i don't see how it would work at all. Long integers will only accept 10 digits (sorta... as long as it's less then 2,147,483,647)

When u say "I have tried using every format option.", do u mean u changed the Format option? i.e. General, currency, percentage etc... or the Field size option? i.e. Integer, Long Integer, Single, Double, etc... ?

If you know which record was causing the problem, try entering that record directly into the table... at least then it might tell u what the specific problem is?

 

[toddbingham]

I do not know which record exactly as there are several records. When I say it is 12 digits, it is 012345678900
Usually the first 4 digits are leading zeros. I wold want it to format it to look like 0123456789.00
And I have tried changing from Currency, Number, etc., aas well as, Long, General, Currency, etc.
This morning when I came in, it appended fine. Now the next time that I run the query, it may not work. That is what baffles me.

Thanks.

 

[toddbingham]

OK, figured out when it happens. It happens after a delete query is ran to clear the contents of the table. The next time the append query is ran, it gives the numeric field overflow error. Any ideas?

 

[The_Doc_Man]

Just an off chance here. Probably isn't the case, but it is worth asking.

By any chance does your append query include an expression that does a domain aggregate on the table you just cleared, and does that expression include a divide by a DSum, DMax, DMin, or DCount?

You ought to get a different error than "overflow" if you were in VBA. I would have expected an explicit "divide by zero." But I don't know what the query would do.

 

[toddbingham]

No expressions, just a typical append query. I tries something that has seemed to fix whatever was going wrong. The append query actually moves records from a link table. The link table format had the field being imported as currency. I just changed it to text and this seems to solve whatever problems were happening. The problem actually seems to be in the link file. Dunnowhy, but I will go with it.

Thanks.