Fixing Numeric Overflow

[Guest]

I've built an append query to add linked data to a table in Access. The link
data has a numeric field and had been predetermined by Access 2003 to be
integer. I'm encountering numeric overflow when I try to append the data.
It's very likely that I have numbers over the integer limit. Is there a way
to convert the data to long?

To temporary overcome the problem, everytime before I press the append
button, I have to make some changes to the data (ie in Append Query, in the
numeric field, add +1-1 and then look at the data).

Thanks,
Carmen

 

[John W. Vinson/MVP]

I've built an append query to add linked data to a table in Access. The
link
data has a numeric field and had been predetermined by Access 2003 to be
integer. I'm encountering numeric overflow when I try to append the data.
It's very likely that I have numbers over the integer limit. Is there a
way
to convert the data to long?

Sure. Rather than appending the linked field value directly, wrap it in a
CLng() expression:

AppNum: CLng([fieldname])

Note that this will fail too if the field is greater than two billion odd
(the limit for long integers); are you *really* doing calculations with
numbers of this size? If the "numbers" are actually identifiers, could you
change the datatype of the field to Text?

John W. Vinson/MVP