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
|
|||
|
|||
#num!
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? |
#2
|
|||
|
|||
#num!
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? |
#3
|
|||
|
|||
#num!
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? |
#4
|
|||
|
|||
#num!
On Mon, 26 Apr 2010 16:19:01 -0700, joemeshuggah
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] |
#5
|
|||
|
|||
#num!
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 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] . |
#6
|
|||
|
|||
#num!
On Mon, 26 Apr 2010 19:06:01 -0700, joemeshuggah
wrote: 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 You'll need to use the import wizard to specify the datatypes of the fields, then; if you just do a blind import Access will see what looks like numbers in the file and (incorrectly) guess that the fieldtype is number. You will need to create an Import Specification. It's tedious but not too difficult - use the Advanced button on the import text wizard. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|