Numeric Field Overflow Error

Hello,

I have a table in my database that is liked to an excel file. The data type
of every field of the linked table is 'text'. I have created another table
that I will append the information to via an append query. This secondary
table has the same fields and the same data types - all 'text'. When I try to
run the append query, it may work one time and then error out saying,
"Numeric Field Overflow."

My question is - How do I fix this error so that the query will run
consistently? Why does it sometimes append and othertimes give me the
'Numeric Field Overflow' error?

(If it is relevant, my live table contains contact information for our
warehouses including addresses, phone numbers, contact names. It has
information for the US and Canada, so it has zip codes and postal codes.
Those fields are all set to text in the excel file that is linked to the
database. I am using Access 97 and the excel file is in Excel 2003)

Any help or guidence would be greatly appreciated.

Thank you!!

On Fri, 30 Apr 2010 13:34:02 -0700, Tracy
wrote:

Hello,

I have a table in my database that is liked to an excel file. The data type
of every field of the linked table is 'text'. I have created another table
that I will append the information to via an append query. This secondary
table has the same fields and the same data types - all 'text'. When I try to
run the append query, it may work one time and then error out saying,
"Numeric Field Overflow."

My question is - How do I fix this error so that the query will run
consistently? Why does it sometimes append and othertimes give me the
'Numeric Field Overflow' error?

(If it is relevant, my live table contains contact information for our
warehouses including addresses, phone numbers, contact names. It has
information for the US and Canada, so it has zip codes and postal codes.
Those fields are all set to text in the excel file that is linked to the
database. I am using Access 97 and the excel file is in Excel 2003)

Any help or guidence would be greatly appreciated.

Thank you!!

Excel doesn't provide datatypes for its cells. Regardless of the datatype you
specify in your Access table, Access must guess at the datatype of the linked
spreadsheet cells. Something like a telephone number (10 digits) can easily
exceed the range of a Long Integer - but if the program sees 2014445555 in the
first row of a spreadsheet, it will "helpfully" guess that it's a number. When
it hits 8052223333 later in the sheet... bang, overflow error!

One solution is to edit the sheet to put ' before all such fields; another is
to put a dummy row at the top of the sheet with a text value in each cell (and
discard this row during or after import).
--

John W. Vinson [MVP]

Thank you for your response John, I do have an additional question for you -

When I initially link the excel table to Access, Access creates a table
where all fields in the table are a text data type. The table that I created
to append the data to, from the live table, is an exact copy. We do this so
that we can have multiple people viewing the form at once, when we had the
form pulling directly from the live table only one person could be in it at
once, which does not work for the department.

I did try your suggestion of putting a strictly text field in the first
record of the table, but it gave the same error. The append query worked
once, then I deleted it and went to run it again to test it, and then it gave
me the 'Numeric Field Overflow' error.

If you could provide a fix that would allow for more than one person to view
the data at once, when looking at a form from a live table, that could fix
the problem. The reason we have the table in excel is so that numerous
departments can access it and that we only have to update once source,
instead of updating many and having duplicate information that may or may not
match up.

Any suggestions or questions you have for me would be greatly apprecaited.

Thank you!!



"John W. Vinson" wrote:

On Fri, 30 Apr 2010 13:34:02 -0700, Tracy
wrote:

Hello,

I have a table in my database that is liked to an excel file. The data type
of every field of the linked table is 'text'. I have created another table
that I will append the information to via an append query. This secondary
table has the same fields and the same data types - all 'text'. When I try to
run the append query, it may work one time and then error out saying,
"Numeric Field Overflow."

My question is - How do I fix this error so that the query will run
consistently? Why does it sometimes append and othertimes give me the
'Numeric Field Overflow' error?

(If it is relevant, my live table contains contact information for our
warehouses including addresses, phone numbers, contact names. It has
information for the US and Canada, so it has zip codes and postal codes.
Those fields are all set to text in the excel file that is linked to the
database. I am using Access 97 and the excel file is in Excel 2003)

Any help or guidence would be greatly appreciated.

Thank you!!

Excel doesn't provide datatypes for its cells. Regardless of the datatype you
specify in your Access table, Access must guess at the datatype of the linked
spreadsheet cells. Something like a telephone number (10 digits) can easily
exceed the range of a Long Integer - but if the program sees 2014445555 in the
first row of a spreadsheet, it will "helpfully" guess that it's a number. When
it hits 8052223333 later in the sheet... bang, overflow error!

One solution is to edit the sheet to put ' before all such fields; another is
to put a dummy row at the top of the sheet with a text value in each cell (and
discard this row during or after import).
--

John W. Vinson [MVP]
.

On Mon, 3 May 2010 14:38:01 -0700, Tracy
wrote:

Thank you for your response John, I do have an additional question for you -

When I initially link the excel table to Access, Access creates a table
where all fields in the table are a text data type. The table that I created
to append the data to, from the live table, is an exact copy. We do this so
that we can have multiple people viewing the form at once, when we had the
form pulling directly from the live table only one person could be in it at
once, which does not work for the department.

I did try your suggestion of putting a strictly text field in the first
record of the table, but it gave the same error. The append query worked
once, then I deleted it and went to run it again to test it, and then it gave
me the 'Numeric Field Overflow' error.

This is peculiar. If you're appending to Text fields I would not expect this
error at all! Doublecheck the structure of the target table: is every field in
fact a text datatype? Could you post the SQL of the query?

If you could provide a fix that would allow for more than one person to view
the data at once, when looking at a form from a live table, that could fix
the problem. The reason we have the table in excel is so that numerous
departments can access it and that we only have to update once source,
instead of updating many and having duplicate information that may or may not
match up.

And this seems wrongheaded! Choosing Excel so that multiple users can share
the same data is exactly the opposite of what I'd expect; Access is multiuser
by default, Excel is one user only by default. If you have data that you want
multiple users to share, Access would seem to be the preferred repository. You
can use read-only forms if you want to protect the data from updating.
--

John W. Vinson [MVP]

The reason my boss chose to link to the excel file is that the information is
used by multiple departments, but the database that we use the information in
is only used by our department. So when there is an update, we make it on
the excel file and then run the append/delete query to update the append
table in the database. The excel file is saved on a common drive but the
database my particular department uses is only used by us, so its kind of
backwards from what you'd think it should be.

Here is the SQL of the append query and the query I use to run the form off
of.

Append Query:
INSERT INTO WarehouseInfo_Live
SELECT WarehouseInfo_Live_link.*
FROM WarehouseInfo_Live_link;

Warehouse Info Display Query:

(The 4 digit WhseID is the item that is selected via a combo box, and then
the related records are displayed on the form. It pulls from the table the
data from the live table is appended to.)

SELECT WarehouseInfo_Live.WhseID, WarehouseInfo_Live.WhseName,
WarehouseInfo_Live.AddressLine1, WarehouseInfo_Live.AddressLine2,
WarehouseInfo_Live.City, WarehouseInfo_Live.State, WarehouseInfo_Live.Zip,
WarehouseInfo_Live.SpeedDial, WarehouseInfo_Live.Phone1,
WarehouseInfo_Live.Phone2, WarehouseInfo_Live.Fax,
WarehouseInfo_Live.Contact1, WarehouseInfo_Live.Email1,
WarehouseInfo_Live.Contact2, WarehouseInfo_Live.Email2,
WarehouseInfo_Live.Contact3, WarehouseInfo_Live.Email3,
WarehouseInfo_Live.WhseNumber, WarehouseInfo_Live.[Self Billing],
WarehouseInfo_Live.InvPrefix, WarehouseInfo_Live.CutOff,
WarehouseInfo_Live.HoursofOperation, WarehouseInfo_Live.[Important Notes],
WarehouseInfo_Live.[FEDEX Cutoff], WarehouseInfo_Live.DSO1,
WarehouseInfo_Live.DSO2, WarehouseInfo_Live.Billing,
WarehouseInfo_Live.Inventory, WarehouseInfo_Live.Credit,
WarehouseInfo_Live.Deployment, WarehouseInfo_Live.Transportation,
WarehouseInfo_Live.TransportationAnalyst,
WarehouseInfo_Live.ExpressWhsePlanner, WarehouseInfo_Live.CustomerService
FROM WarehouseInfo_Live;

Thank you again for your help, I appreciate the quick responses too!


"John W. Vinson" wrote:

On Mon, 3 May 2010 14:38:01 -0700, Tracy
wrote:

Thank you for your response John, I do have an additional question for you -

When I initially link the excel table to Access, Access creates a table
where all fields in the table are a text data type. The table that I created
to append the data to, from the live table, is an exact copy. We do this so
that we can have multiple people viewing the form at once, when we had the
form pulling directly from the live table only one person could be in it at
once, which does not work for the department.

I did try your suggestion of putting a strictly text field in the first
record of the table, but it gave the same error. The append query worked
once, then I deleted it and went to run it again to test it, and then it gave
me the 'Numeric Field Overflow' error.

This is peculiar. If you're appending to Text fields I would not expect this
error at all! Doublecheck the structure of the target table: is every field in
fact a text datatype? Could you post the SQL of the query?

If you could provide a fix that would allow for more than one person to view
the data at once, when looking at a form from a live table, that could fix
the problem. The reason we have the table in excel is so that numerous
departments can access it and that we only have to update once source,
instead of updating many and having duplicate information that may or may not
match up.

And this seems wrongheaded! Choosing Excel so that multiple users can share
the same data is exactly the opposite of what I'd expect; Access is multiuser
by default, Excel is one user only by default. If you have data that you want
multiple users to share, Access would seem to be the preferred repository. You
can use read-only forms if you want to protect the data from updating.
--

John W. Vinson [MVP]
.

On Tue, 4 May 2010 11:50:01 -0700, Tracy
wrote:

The reason my boss chose to link to the excel file is that the information is
used by multiple departments, but the database that we use the information in
is only used by our department. So when there is an update, we make it on
the excel file and then run the append/delete query to update the append
table in the database. The excel file is saved on a common drive but the
database my particular department uses is only used by us, so its kind of
backwards from what you'd think it should be.

ok... makes sense if you're dealing with benighted, abominable heathens who
rely on Excel bg

Here is the SQL of the append query and the query I use to run the form off
of.

Append Query:
INSERT INTO WarehouseInfo_Live
SELECT WarehouseInfo_Live_link.*
FROM WarehouseInfo_Live_link;

Warehouse Info Display Query:

(The 4 digit WhseID is the item that is selected via a combo box, and then
the related records are displayed on the form. It pulls from the table the
data from the live table is appended to.)

What does the combo box have to do with anything??? Neither query references
it.

SELECT WarehouseInfo_Live.WhseID, WarehouseInfo_Live.WhseName,
WarehouseInfo_Live.AddressLine1, WarehouseInfo_Live.AddressLine2,
WarehouseInfo_Live.City, WarehouseInfo_Live.State, WarehouseInfo_Live.Zip,
WarehouseInfo_Live.SpeedDial, WarehouseInfo_Live.Phone1,
WarehouseInfo_Live.Phone2, WarehouseInfo_Live.Fax,
WarehouseInfo_Live.Contact1, WarehouseInfo_Live.Email1,
WarehouseInfo_Live.Contact2, WarehouseInfo_Live.Email2,
WarehouseInfo_Live.Contact3, WarehouseInfo_Live.Email3,
WarehouseInfo_Live.WhseNumber, WarehouseInfo_Live.[Self Billing],
WarehouseInfo_Live.InvPrefix, WarehouseInfo_Live.CutOff,
WarehouseInfo_Live.HoursofOperation, WarehouseInfo_Live.[Important Notes],
WarehouseInfo_Live.[FEDEX Cutoff], WarehouseInfo_Live.DSO1,
WarehouseInfo_Live.DSO2, WarehouseInfo_Live.Billing,
WarehouseInfo_Live.Inventory, WarehouseInfo_Live.Credit,
WarehouseInfo_Live.Deployment, WarehouseInfo_Live.Transportation,
WarehouseInfo_Live.TransportationAnalyst,
WarehouseInfo_Live.ExpressWhsePlanner, WarehouseInfo_Live.CustomerService
FROM WarehouseInfo_Live;

And are the fields in WarehouseInfo_Live in fact all Text fields? The error
message sounds like you're trying to insert too large a number into a Number
type field: might one of the Phone fields, or some other field consisting of
all digits, actually be a Number field?
--

John W. Vinson [MVP]