Exporting more than 32,767 characters to Excel from memo field

[derekbrown]

Hi,

The title gives away the problem I am experiencing! I have memo fields that have more than 32,767 characters so when I try to export this to Excel I encounter problems.

How can I check the length of the memo field and split the data so that it will show in an additional column on export to excel?

I've had a look on the forums and have not been able to find the answer I need. I have also tried writing a report that splits the data into seperate fields (then analyze it with Excel) but then I have the problem that the data in each field is truncated to 255 characters. To get some of the memo fields exported I would need more columsn than Excel has.

Cheers

Derek

 

[neileg]

Mmm...

I'm a bit puzzled as to why you would want to do this...

You could set up a query that has a series of calculated fields that use Left() and Mid() to chop up your text field, but in my experience, dealing with strings of more than 255 characters in a query can be difficult.

I'm fairly confident that the amount of data in the memo field indicates a design flaw. Perhaps you should be storing this data in linked text files instead of a memo field.

 

[derekbrown]

Thanks for the response. I'd be interested to hear more on how you think the design can be bettered using linked text fields as I have no experience in that area. Would you be able to provide some guidance or point me in teh right direction?

Cheers

 

[neileg]

To be honest I've never done this. I'm just a hacker, not a trained programmer. You could have a look at using a hyperlink to a text or a word file. Or you could hold the path to the files as a text field in the db.

How come the memo fields are so big?

 

[derekbrown]

Unfortunately, not a programmer either. ) Although I have looked at using links with the FollowHyperlink method to link to external docs.

The reason that the memo fields are so big is that they contain step by step test scripts, some of which are rather lengthy. Unfortunately I wasn't involved in writing them, I just got a big chunk of them to pull into the database from a Word doc. I think they could probably be broken down a little better. What would you usually set as the absolute limit on characters in a memo field? Since Access defines that they can hold 64,000 characters I simply assumed that this would be the best control for the job, but I'd be interested to hear if there good reasons that filling them up in this way ss not a good idea.

Thanks again!

 

[boblarson]

How are you trying to export to Excel?