How do i fix a type conversion failure?

[Shelley]

While importing a text document I get the error "type conversion failure."
It imports all of the fields except for one field, and it is a number field,
about 15 characters long. The information being imported is from a text
document, delimited by columns. Any help would be much appreciated.
Thank you,

Shelley

 

[Jeff Boyce]

Shelley

"... about 15 characters long..." is quite a number. Is it actually a
number you'd do math on, or is it a "number" in the sense of a "membership
number"?

Any chance you can import it as text first, then convert it using a query?

By the way, do you actually need to import (a copy of) it, or could you link
to the data?

Regards

Jeff Boyce
Microsoft Office/Accesss MVP

 

[Shelley]

It is more like a membership number, specifically a receipt number. It does
need to be imported. I am not sure how I would import as text and then
convert using a query, if you could advise that might be helpful.

Thanks,

 

[Jeff Boyce]

Shelley

Import it (or link to it) 'as is'.

Create a new table in Access (or more than one, depending on how you are
normalizing your data ... you ARE normalizing your data, right?!). Make the
"membership number" (which is actually a membership 'code', since you aren't
doing math on it) a text field in the table definition.

Use queries to append from the import/link data to the final, more permanent
table structure. In the query(ies), use something like the CStr() function
to convert from "number" to text.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Roger Carlson]

Since it's not really a number, import it as text and leave it as text.
When you use the Import Wizard to import text data, one of the screens
allows you to modify the datatype that the wizard has guessed tyou want. Go
to the Advanced button and save this as an Import Specification. If you use
this specification every time you import it, it will import it as a text
field each time.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L