If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
Numeric Field Overflow Error
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] |
#3
|
|||
|
|||
Numeric Field Overflow Error
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] . |
#4
|
|||
|
|||
Numeric Field Overflow Error
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] |
#5
|
|||
|
|||
Numeric Field Overflow Error
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] . |
#6
|
|||
|
|||
Numeric Field Overflow Error
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] |
Thread Tools | |
Display Modes | |
|
|