|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
#Num! numeric overflow query problemThis one's driving me mad. I am trying to 'copy' a linked table into my database. The table is three fields: two that are text fields and one that is a number. The first record is dodgy and has a empty second text field and #Num! instead of a number. I cannot change these as it's a linked table. I can't use CopyObject because it just copies the linked table as another linked table. I have set up a MakeTable query in which the criteria is Is Not Null for the blank field and >0 for the number field. This runs OK the first time (correctly ignoring the dodgy record) but subsequent times it falls over with a numeric overflow error. If I edit the query in any way and run it again it runs once correctly and then errors every other time. I got so frustrated with this that I decided a different approach. I created a blank table and used a delete query to clear it, followed by an append query (from the linked table, with the above criteria) to refresh it. The delete query runs fine every time but the append query only runs once correctly and then errors every other time!! Anybody got any ideas of what else I could try? Thanks! Hi Andy,
If you are trying to bring a table from one access database to another then you want to 'import' it. In 2003 (and earlier, I think), it's File>Get External Data>Import. In 2007, it's External Data>Access I think you would want to go to the file that has the actual table.......not the file that has a link to the table in it. Hope that helps, CW Show quote "Andy" wrote: > Hi > > This one's driving me mad. > > I am trying to 'copy' a linked table into my database. The table is three > fields: two that are text fields and one that is a number. The first record > is dodgy and has a empty second text field and #Num! instead of a number. I > cannot change these as it's a linked table. > > I can't use CopyObject because it just copies the linked table as another > linked table. > > I have set up a MakeTable query in which the criteria is Is Not Null for the > blank field and >0 for the number field. This runs OK the first time > (correctly ignoring the dodgy record) but subsequent times it falls over > with a numeric overflow error. If I edit the query in any way and run it > again it runs once correctly and then errors every other time. > > I got so frustrated with this that I decided a different approach. I created > a blank table and used a delete query to clear it, followed by an append > query (from the linked table, with the above criteria) to refresh it. The > delete query runs fine every time but the append query only runs once > correctly and then errors every other time!! > > Anybody got any ideas of what else I could try? > Thanks! > > > Get someone to fix the linked table. If it's Access, there's a very good
chance that the table is corrupt. -- Show quoteJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Andy" wrote: > Hi > > This one's driving me mad. > > I am trying to 'copy' a linked table into my database. The table is three > fields: two that are text fields and one that is a number. The first record > is dodgy and has a empty second text field and #Num! instead of a number. I > cannot change these as it's a linked table. > > I can't use CopyObject because it just copies the linked table as another > linked table. > > I have set up a MakeTable query in which the criteria is Is Not Null for the > blank field and >0 for the number field. This runs OK the first time > (correctly ignoring the dodgy record) but subsequent times it falls over > with a numeric overflow error. If I edit the query in any way and run it > again it runs once correctly and then errors every other time. > > I got so frustrated with this that I decided a different approach. I created > a blank table and used a delete query to clear it, followed by an append > query (from the linked table, with the above criteria) to refresh it. The > delete query runs fine every time but the append query only runs once > correctly and then errors every other time!! > > Anybody got any ideas of what else I could try? > Thanks! > > >  |
|||||||||||||||||||||||
Other interesting topics