Overflow Error when increment # to 32768

[Paul Fenton]

We have a table in an application that increments a field by 1 every
time a new subject is entered. The current number in this field is
32767. When we try to add a new subject, we get "Error 6: Overflow"
on the line that increments that field by 1 to 32768.

The field in question is a number field, Long Integer, which I thought
could store #'s to 2,147,483,647. This behavior is as if the field
were an Integer field.

Why is this happening and how do I correct it?


Paul Fenton
(e-mail address removed)

 

[Ken Snell [MVP]]

I assume that you're trying to add this record via programming? If yes,
check that the variable that you use to "get" the next incremental value is
also dim'd as a long integer, and not as an integer.

 

[Allen Browne]

Couple of possibilities, Paul.

Is there any kind of validation rule on the field (or the table) where the
number could be interpreted as an integer?

Any relation to another table where the related field is integer?

If not, and the error occurs even when you enter a value into the table,
then it probably represents a corruption. This sequence should solve it:
Turn off Name AutoCorrect.
Compact the database.
Select the table in the database window.
Copy (Ctrl+C) and paste (Ctrl+G), pasting structure only, and giving a new
name.
Delete the problem field. Save. Compact again.
Open the new (blank) table, and create the field again as a Long Integer.
Save.
Use an Update query to populate the new table from the old.
Delete any relationships the old one was involved in.
Delete the old table.
Rename the new one to the old.
Compact again.
Recreate the relations.

 

[Paul Fenton]

Ken and Allen, you're both right on the money. I found a couple of
instances where I had defined some vars as Integer instead of Long. I
changed that and the problem's gone.

Thank you both!

Paul Fenton

 

[Ken Snell [MVP]]

You're welcome.