When exporting a report or query to Excel how can I get a memo field over 255 charac

[Tennotrumps]

I am exporting a report to Excel, but memo fields are being cut to 255 characters in length (as if they were text fileds). How do I get around this?.

[NeoPa]

Instead of exporting the data directly, create a query that shows the data and uses functions to return no more than the amount of data that an Excel cell can handle.

Messy, but then you're using Memo fields so you've already limited your options.

[Tennotrumps]

@NeoPa
I've checked, and an excel cell can handle more than 255 characters so I'm not sure why it cuts the data off at 255 characters. We need the field to contain more that 255 characters so I can't make it a text field? 255 characters is the largest text field you can hace isn't it? So I figure that somehow the export function is taking the memo field to be a txt field? Is this the case?

[NeoPa]

@Tennotrumps
Because it's a Memo field I think. Read my post.
@Tennotrumps
You're not following the logic well here. Text fields and Excel cells can both handle >255 chars. Text fields aren't the problem (hence my post).
@Tennotrumps
No.
@Tennotrumps
No.

I just started some tests to ensure I was giving you accurate information. I found that my Memo field data was not truncated at 255 chars at all. I don't know what's happening to you, but I had text strings in my spreadsheet reaching 2,427 characters. I'm using Access & Excel 2003. What are you using? Particularly, which export format are you using?

[Tennotrumps]

I'm using Acsess 2007 and Excel 2003. I was wondering if I could change the memo field to a text field and if that would fix the problem. If there isn't a limit of 255 characters would that be the simplest way to fix it? I am using a report based on a query and then exporting this to excel. I will try exporting the query direct, however the report appears to work better for what we are doing.

Thanks for your help

[NeoPa]

It seems I should apologise. Text fields are indeed limited to 255 characters. I've been feeding you wrong information all this time.

Having said that, I suspect that the reason this is failing is that the controls (rather than the fields) are limiting the data to 255 characters. Controls are objects like TextBoxes, ComboBoxes & ListBoxes that you find on Form or Report objects.

What you may like to consider, if it's a report you're exporting from, is to have various hidden controls to allow the export of all the data, but not show when the report is displayed or printed. This is assuming that exporting the underlying query is not good for you due to things like positions on the page of various controls.

So for instance, the query - assuming a field called [MemoField] - might be changed to :

SELECT ...,
       [MemoField],
       Mid([MemoField],1,255) AS [MF1],
       Mid([MemoField],256,255) AS [MF2],
       Mid([MemoField],511,255) AS [MF3],
       ...
FROM   ...

[MF1], [MF2] & [MF3] (and any others) would be 'shown' in hidden controls on the report. These should be included in the export, but if they are not, then you may need to consider making them visible, but put them behind something else on the report so they don't display.

[Tennotrumps]

I've retired simply exporting teh query and thedata goies through ok that way so I think I'll just rework teh query a little so it exports in teh format we need. That is by far the simplest way. I'm afraid your instructions above are a bit over my head - though I'll keep them in mind for when a I have a bit more time.

Many thanks