Export to Excel doesn't retain Lookup Values

[riggsd]

I have several fields that lookup the field value from other tables and it works fine as long as I'm in Access. However, when I export the table/query to Excel, those fields come into Excel with the lookupID instead of the value.

I've been getting around this by selecting the entire query results, copying, and pasting into Excel but this is getting excessive since I have approximately 230,000 attributes in the query results. This is in addition to having to manually copy the memo fields, paste into Word, then copy and paste into Excel in order to get the entire contents of the field.

Is there any way to save the data to Excel so that the lookup values are retained instead of the lookupID?

 

[Uncle Gizmo]

I believe this post should go some way to providing you with an answer.

 

[riggsd]

Thanks, I've tried it and posted a question.

 

[Mackinnon]

Copy and Pasting Memo Fields

I know that your query had nothing to do with memo fields but I noticed that you are cutting and pasting memo fields into excel.
I had a problem with this and The Doc Man came up with a quick and easy solution. See Below.

I think the problem is that if you used a wizard to CREATE a table, you are hosed because the Wizard rarely chooses type MEMO for a field. And a field of type TEXT stops at 255 characters.

But if you pre-create an empty table with MEMO fields already defined and then APPEND to the existing, empty table, you might have better luck.

Particularly if only a few fields late in the table are very long, the import wizard will never figure out that it needs a memo field. I believe this appeared once in this forum or maybe I found it in an external article. The wizard only reads about the first 10 records or so in order to estimate the size and type needed for each field in the import. So if your first few records are below the 255 mark, the wizard will not assume you need it.

Anyway, hope that info helps and good luck with your initial query

 

[riggsd]

The table was built manually with the fields set to Memo and then the data imported in, however, the Memo fields data wasn't imported but added using copy/paste.