Some memo-field text lost when in Table view

[Lester]

I have a MEMO field in a FORM where we occasionally uses a carriage return to start another line. When I view the same database as a TABLE, and look in the memo field, all the text after the carriage return is missing. When we go back to FORM view, all the text is still there. Since we need to export the table as a text file, this is a serious problem.



Where carriage returns have not been used in filling out the memo field on the form, nothing is missing in TABLE view.

When I open the database, the heading says Database: (Access 2007-2010)

I am a user, not a programmer. Your help would be gratefully appreciated.

[June7]

Are you sure the data is missing? Did you put cursor in the field and right arrow through the string and past the CR?

Did you try the export? Is the data truncated?

[Lester]

Yes, the data is truly missing. I have put the cursor in the field in the TABLE, moved it far to the right; nothing is there. On exporting the table as a text field, the same data is missing, exactly as it shows in the TABLE in Access.

[Dal Jeanis]

Hmmm. Unfortunately, most of the questions I need to ask and my debug tips for this may be more for programmer types.

There's a couple of basic ones -

1) Are you exporting the table itself, or exporting a query that reads from the table?

2) If a query, then
A) open the query in design view (right-click the query in the nav pane, select design view),
B) switch to SQL view (query tools design tab, top left for Results Group, View->SQL),
C) copy the query SQL (highlight and control c), and
D) paste it (control v) into a post in this thread.

We'll look and see if there's anything obvious wrong with the query.

[June7]

This is odd. I have memo fields and they are not truncated when I examine directly in the table and they have CrLf. I tested export with wizard and Excel did not lose anything, however the text export did truncate.

[Dal Jeanis]

I saw a couple of items out there regarding what you have to do to make sure that the excel column at the far end of the export is a memo type field. I also reviewed Allen Browne's list of what makes memos truncate at http://allenbrowne.com/ser-63.html, which included some items that made me want to see the query.

I'm tending to believe that it may be something having to do with the representation of CR+LF.

[Lester]

We are exporting the table itself. But I assume if part of the data is missing from the MEMO field in TABLE view, it will also be missing in the exported file.

There is a query that is used to update one of the fields in the table (called DUMMY FIELD) from another field (called our NUMBER field); both are TEXT fields; this query seems to work properly, and has no relationship to the problem MEMO field. But just in case, here is the query: NEGTEST is the name of the TABLE

UPDATE NEGTEST SET NEGTEST.[Dummy Field] = [Number] & ".jpg";

[June7]

Why bother with saving a calculated value that can be generated when needed?

How is the CrLf embedded in the text string? As stated, I have no problem viewing the entire string directly in table and exporting table to Excel.

Do you want to provide db for analysis? Follow instructions at bottom of my post.

[Lester]

Moderator,

I very much appreciate your offer to look at the database. It will take some time to delete proprietary information, shrink it down and provide some notes. I'll send it along after the holiday weekend. Have a tender turkey.

Lester