Database Table Cut Off Text When Transfer From Excel

[d1004]

I have a question concerning why the Access table cut off the text when I transfer the text from an Excel spreadsheet. I set the field as a Memo datatype, but it cuts off the text even though the amount of text is small. Please help as to why it is doing this, and if you have any suggestion to stop it or prevent it. Thank you very much.

Diana

 

[Bastien]

put quotes around the item

insert into table values('" & oexcel.cells(1,1) & "')

hth

Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[d1004]

Bastien,
Do I insert that code into my Access table property? You mean in the Validation Rule property? Just to clarify things, I transfer this text by using Access import feature. After I successfully imported, some of the text got cut off like part of the last sentence got cut off.
Thank you again.

-Diana

 

[Bastien]

the import probably has the space box ticked of in the when the import is done, untick that box an you should be okay...sorry thought you were writing to the DB via ASP...
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[Garridon]

It's a design issue in Access (unresolved in 2002). I actually got a Knowledge Base article created for this. Access is exporting the file as an Excel 95 spreadsheet, which is only able to handle 255 characters. No matter what choice you select, it will not export it in anything later than 95 (when you save it, it says it is an earlier version of Access).

There is a fix, but it's really, really ugly. You literally have to split the field up and concatonate it back together again.

Insert this into an unbound field. The numbers represent the number of characters in the field.

=Mid([memNotes],1,255)

Then do another one for the next 255 characters.

Finally, add an unbound field following these (this creates an empty column in Excel).

Then in Excel you have to Concatenate them together again into the empty field. You'd probably need to create a macro to do that.

This is a really ugly solution, but it's the only one that will work. Your best bet is to find another way of doing it, if you can.
Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com