#num!

[joemeshuggah]

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?

 

[KARL DEWEY]

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.

 

[joemeshuggah]

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.

 

[John W. Vinson]

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.

 

[joemeshuggah]

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]

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.