Numeric Field Overflow Error
in [Queries]
|
From: Tracy on 30 Apr 2010 16:34 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!!
From: John W. Vinson on 30 Apr 2010 17:38 On Fri, 30 Apr 2010 13:34:02 -0700, Tracy <Tracy(a)discussions.microsoft.com> 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]
From: Tracy on 3 May 2010 17:38 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 <Tracy(a)discussions.microsoft.com> > 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] > . >
From: John W. Vinson on 4 May 2010 00:50 On Mon, 3 May 2010 14:38:01 -0700, Tracy <Tracy(a)discussions.microsoft.com> 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]
From: Tracy on 4 May 2010 14:50
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 <Tracy(a)discussions.microsoft.com> > 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] > . > |