Solved: Memo Field Strangeness

[CreganTur]

I've got a procedure that worked for over a year that is now breaking, and I have no idea why.

The code creates a linked table out of a chosen Excel workbook, runs some validation on the contents of the table, and then appends the records to an existing table in Access. One of these fields is a memo field- it holds detailed comments from an agent regarding a record.

When the memo field was viewed in a form, you saw the entire contents of the field.

Now, however, the memo field is being split. When you view it on the form, the text is cut off randomly - and I do mean random. I've checked the length of the truncated pieces, and it is never the same. Also, there isn't a similar character at the splits.

When I look at the contents of the memo field in the table itself, I find the same thing- the text stops. I'm moving through the text by highlighting it and dragging my cursor to move through the text. When I get to the end of the line, where the text is split, I can move my cursor down, which will drop me to a new line within the memo field. I find the missing text (the rest of the memo field) on this line... but it will not show up on the form.

I can reporduce this error by importing the spreadsheet into the Access table using File->Import, TransferText also causes the error, changing the data into a text file and then importing it also causes this error.

Has anyone run into something like this before?

[OBP]

Yes it is usually caused in Excel when the column has a Line feed in it splitting the line over rows in the Cell.
The Character is not visible in Access, but you may be able to find it using VBA.

[CreganTur]

Yes it is usually caused in Excel when the column has a Line feed in it splitting the line over rows in the Cell.
The Character is not visible in Access, but you may be able to find it using VBA.

There aren't any line feeds or carriage returns within the memo field text. I wish it was a simple fix like that.

[OBP]

Can you provide me with an Example to "play" with?

[CreganTur]

Can you provide me with an Example to "play" with?

I'll develop one and post it in a little while.

[CreganTur]

Well, I've finally found what's cuasing the error...I just don't know why it's happening.

While I was trying to create an example DB for you, Tony, I discoverd that I could not recreate the error. After some more testing I realized that this error is occuring because I am pulling the data from a query that joins 2 tables together. Somehow that is blocking the memo field from showing data after the line break.

I've been thinking about redesigning these tables, since this DB was built when I was still learning a lot, so I can definately improve on it.

Very strange... but at least I know that I can fix it by redesigning the tables.

[OBP]

Randy, that is good news. I have also seen a problem where a memo field has been limited to displaying only 255 characters as well, but I can't remember the exact circumstances now.

[CreganTur]

Randy, that is good news. I have also seen a problem where a memo field has been limited to displaying only 255 characters as well, but I can't remember the exact circumstances now.

I've heard of that, too. Originally, I thought that was what I was experiencing... until I tested the length of the text showing in the memo field and found that it was greater than 255 for most of the fields. In fact, when I ran a Len function against them, I found that they were random lengths.

Don't know how/why a join could cause this... especially after it's been working without a problem for more than a year, but whatever.

[OBP]

Randy, that is Access for you, always full of surprises, glitches and Bugs.
But I still love it.