#num!
in [Queries]
|
From: joemeshuggah on 26 Apr 2010 19:04 is there a way to get around this?? i have an access database that is set up with a link to a text file that is created via teradata sql assistant. this text file is used to create (and once created, append going forward) a table within access. my problem is that access is not reading the text file correctly. there are a few fields, particularly telephone number and a few customer identification numbers that do not read correctly in the linked text file. the source text file reads fine, but the linked version in access shows #NUM! ive checked around, and tried using quotes for the output dataset from teradata, but that only creates another problem since a number of the other fields in the text file that is output have these characters as well. is there a way around this issue?
From: KARL DEWEY on 26 Apr 2010 19:13 >>there are a few fields, particularly telephone number and a few customer identification numbers that do not read correctly in the linked text file. The problem is that telephone access codes are not numbers. You put puncuation that is not known to be in any kind of number system such as dashes and parenthesis. Telephone numbers never have any mathmatical functions applied such as adding, multiplying, squaring, or rounding. Therefore they have to be handled as text. -- Build a little, test a little. "joemeshuggah" wrote: > is there a way to get around this?? i have an access database that is set up > with a link to a text file that is created via teradata sql assistant. this > text file is used to create (and once created, append going forward) a table > within access. my problem is that access is not reading the text file > correctly. there are a few fields, particularly telephone number and a few > customer identification numbers that do not read correctly in the linked text > file. the source text file reads fine, but the linked version in access > shows #NUM! > > ive checked around, and tried using quotes for the output dataset from > teradata, but that only creates another problem since a number of the other > fields in the text file that is output have these characters as well. > > is there a way around this issue?
From: joemeshuggah on 26 Apr 2010 19:19 actually there are no dashes or any other characters...just numbers. i even tried casting them as char in teradata, but that made no difference. again, just numbers, no dashes or parenthesis whatsoever...they show up fine in the actual text file, but as #num! when linking the file or importing the file to access. "KARL DEWEY" wrote: > >>there are a few fields, particularly telephone number and a few customer > identification numbers that do not read correctly in the linked text file. > The problem is that telephone access codes are not numbers. You put > puncuation that is not known to be in any kind of number system such as > dashes and parenthesis. > Telephone numbers never have any mathmatical functions applied such as > adding, multiplying, squaring, or rounding. Therefore they have to be > handled as text. > > > -- > Build a little, test a little. > > > "joemeshuggah" wrote: > > > is there a way to get around this?? i have an access database that is set up > > with a link to a text file that is created via teradata sql assistant. this > > text file is used to create (and once created, append going forward) a table > > within access. my problem is that access is not reading the text file > > correctly. there are a few fields, particularly telephone number and a few > > customer identification numbers that do not read correctly in the linked text > > file. the source text file reads fine, but the linked version in access > > shows #NUM! > > > > ive checked around, and tried using quotes for the output dataset from > > teradata, but that only creates another problem since a number of the other > > fields in the text file that is output have these characters as well. > > > > is there a way around this issue?
From: John W. Vinson on 26 Apr 2010 21:26 On Mon, 26 Apr 2010 16:19:01 -0700, joemeshuggah <joemeshuggah(a)discussions.microsoft.com> wrote: >actually there are no dashes or any other characters...just numbers. i even >tried casting them as char in teradata, but that made no difference. again, >just numbers, no dashes or parenthesis whatsoever...they show up fine in the >actual text file, but as #num! when linking the file or importing the file to >access. The largest Long Integer (the default datatype for Number) is 2147483647 - so any phone numbers in areacode 215 or above will give you this error. Change your Access table design. These fields should... must!... be of Text datatype, not Number. -- John W. Vinson [MVP]
From: joemeshuggah on 26 Apr 2010 22:06
maybe i dont understand, but if the issue stems from a linked text file, the datatypes cannot be changed....when i try to go into design view it does not allow the change since it is a linked text file and not a table "John W. Vinson" wrote: > On Mon, 26 Apr 2010 16:19:01 -0700, joemeshuggah > <joemeshuggah(a)discussions.microsoft.com> wrote: > > >actually there are no dashes or any other characters...just numbers. i even > >tried casting them as char in teradata, but that made no difference. again, > >just numbers, no dashes or parenthesis whatsoever...they show up fine in the > >actual text file, but as #num! when linking the file or importing the file to > >access. > > The largest Long Integer (the default datatype for Number) is 2147483647 - so > any phone numbers in areacode 215 or above will give you this error. > > Change your Access table design. These fields should... must!... be of Text > datatype, not Number. > -- > > John W. Vinson [MVP] > . > |