an ERROR HANDLING question

[teach314]

I have a table like this...
Field ID is autonumber and pk. Field X is Long.

ID               X 
--------------------------
1            1,271,234,567
2            2,035,222,123     
3            1,997,276,330
4
5            2,009.776,197  
...etc

The values of X are very large and, on occasion, are larger than the LONG upper limit of 2^31 - 1.

If a value of X is too large, I want it to simply be left off the list. In the table above, ID = 4 has a value of 5,844,123,456. In this case, I just want to leave the X value blank.

Here's my problem - when I have a saved query that INSERTs INTO the table, all works as desired. (I do get a message that, say, 17 values had a Type violation, but all of the other values are inserted into the table. BUT, when I try to run this in a VBA module that executes an SQL string, I get run-time error '6': Overflow. No records are added to the table.

QUESTION: How can I use error handling or another technique in VBA to 'ignore' the 'too-large' values while INSERTing the others?

Thanks for any assistance
Teach314

 

[SkipVought]

Hi,

Modify your Query to exclude values in excess of the VBA limit.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]