Access report - truncated "long text" fields in Excel export only

[NacMacNeedle]

I am aware that this seems to be a wide spread problem but could not find a working solution yet.

Setup:
Using Access from Office 2016.
I have a query joining information from a number of tables.
This query is used as a data source to build a report, in which the data is grouped in specific ways, summing totals and means per group etc., all done by report functionality.

The report also contains a number of longer procedure descriptions, stored in long text format.
Long text fields are collected correctly and display fully in the query's data view as well as in the record's. Exporting the report to any format other than Excel keeps the long textfields.
I need the Excel format, to keep the grouping and summing when sending out the preliminary report before sending the final pdf.

Has anyone solved this yet?

 

[NacMacNeedle]

Additional clarification:
- The database I'm using is a single .accdb file
- I try exporting by right-clicking the report in the listing of objects on the left side of the screen, select export and follow the wizard.
- The only options I'm able to choose from are between some 90ies Excel format and some 200...7? Excel format and if I want to auto-open the resulting file, everything else is greyed out.
- This is on a Windows10 machine administrated by the company's IT department. I have no options to update, configure, or install software, and Access-knowledge is non-existent within the IT department.
- Although I'm a seasoned software developer, my knowledge of Access (and indeed Office or Windows) is no more than translating common database schemes and operations via tutorials to the Access GUI.

 

[Joe4]

Long text fields are truncated at 255 characters, when exported to Excel.
There are some workarounds. See this link here for some recommendations: MS Access 2013 truncates longtext fields to 255 characters when exporting to excel?

 

[NacMacNeedle]

Thanks for the answer, Joe4. I know it's none of your fault, but are you seriously telling me Microsoft breaks conversion ability between two of its proprietary core products on purpose? On something as simple as an export?
Your link seems to point at some methods to push data from a query into an Excel sheet. This may be a step into the right direction, but as I need to do more than just copy a query over, it's not really feasible, but I'll keep going that way.

Btw, to just copy a query result I found it quicker to fire up dBeaver (or insert your favourite db administration tool here), connect to the .accdb file, copy-paste the query's sql from Access to a dBeaver sql-input, hit ctrl+enter export the result to .xlsx from there.

 

[Joe4]

It looks like it is an intentional design.

Allen Browne describes it in detail here: Microsoft Access tips: Truncation of Memo fields
(note Allen Browne is an Access guru, and has written lots of great articles on/for Access).

 

[JonXL]

The workaround I use (from Allen Brown's page linked above) is to use VBA to transfer the data to the spreadsheet with a dedicated table containing fields of the expected data type UNION queried onto the top of the export.

 

[NacMacNeedle]

Thanks for your reply, JonXL. This sounds like an interesting approach. Do have a minimal example of how to do this in Access?

My main query boils down to something like

SELECT 
  groupid,
  data1
FROM
  table1
  INNER JOIN table2
  ON table1.lnkTable2 = table2.id

Is there a way to add the `union` query (by groupid, obviously) without breaking the result's ability to be used for the record, or do you suggest processing the query results in a second "export"-query?

 

[rondeondo]

another option is to take your SQL selection and put it into a data link from Excel, so Excel takes the data from Access rather than doing copy/paste. I've done this to enable data extract to Excel so the user doesn't have to open access at all. The limitation is that it doesn't apply any filters that are in place within the access application

 

[JonXL]

Thanks for your reply, JonXL.

...
Is there a way to add the `union` query (by groupid, obviously) without breaking the result's ability to be used for the record, or do you suggest processing the query results in a second "export"-query?

Apologies for the delayed reply.

To set this up, you will create a table with the same fields in the same positions and of the same data types as the query you want to export. Then, use the UNION query to 'stack' the table and your export - a couple ways to do that; suppose you want to just have it in one query, you will update your query to something like this (suppose the dummy table you made is called MyTable):

SELECT
    *
FROM
    [MyTable]
UNION ALL
SELECT
    *
FROM
    (
        SELECT
          groupid,
          data1
        FROM
          table1
        INNER JOIN table2
        ON table1.lnkTable2 = table2.id
    )

Alternatively, you can save your regular query by itself and have, as you suggested, a special one for just the export; suppose your saved query is called 'qryResults':

SELECT
    *
FROM
    [MyTable]
UNION ALL
SELECT
    *
FROM
    [qryResults]

By having your table with the defined long text/memo data type on the field in question selected first in the UNION query, you tell Access to use its data type for that field in the output and it doesn't matter that your table has no data in it because it's just the data type you are interested in using from it.

Let me know if that doesn't work for you...