Make Table Query and Numeric Overflow Errors

[GoKats78]

I have a database linked to an excel spreadsheet. In my database I have a make-table query. When I attempt to run that query I get a "numeric-overflow" error. Ok...so when i open the query in desing mode and change it to a select query then run it my data is returned OK...then I switch back to design mode, change it to a make-table query and run it, the qurey runs fine - it creates the table with no problems.

 

[Darren Bartrup]

By default, a number field is set to a Long Integer allowing numbers from -2,147,483,648 to 2,147,483,647.
I'm not sure if this is true when the table is automatically created. But Access only looks at the first few rows of data to determine the data type at any rate

If one of your cells contains a value higher than the data type will allow you'll get the error.

Try creating your table first and appending the data to it - use a Double data type

I hope that helps

 

[GoKats78]

But why does it work as a select query and not a make-table query?

I can do the double step - but someone else might be using it and not have a clue what to do...that would be my boss...

 

[GoKats78]

I tried to edit but my time limit was up....


All my date in the few few (i think 8 is the number of rows access looks at) is consistent - by that I mean the data in each of the columns is the same; some text, some dates, some numbers.. but each in their own column.

 

[SydneyGeek]

Maybe you could create a dummy record at the top of the sheet, with a numeric value of 50000 where you were getting the overflow. Put DUMMY in one of the text fields.
That should force Access to make the number field Long instead of Integer; you can run a second query to delete the dummy record. By chaining them together the user will never know the difference.

Denis

 

[GoKats78]

I could...but I don't know what record is generating the overflow...

 

[SydneyGeek]

If you suspect that the overflow is numeric, rather than text, my suggestion should fix it because the first several records are used to set the data types and field sizes.
A value of 50000 will ensure that that numeric field is not set as an Integer, which would cause an overflow at 32K.

Denis