MS Access 2010: text in an "RTF" field (actually it is HMTL) shows html code instead of intended formatting

Question

The problem

I am working on a database in MS Access 2010 containing basically text.

Each of my about 4000 records has a memo-field holding a piece of text of about 2000-4000 characters. Over a long period of time I have been highlighting variuos parts of those texts by applying to it, in a form field, things like "bold" or "highlight yellow" or "underline" and so on. This worked fine.

Now the problem: out of a sudden in the older records the formatting is not any more displayed as intended. Instead, I see the respective html tags such as <b>, <u>, <BACKGROUND COLOR =...>, <div></div> and so on.

So, the markup is not lost. It even looks healthy (all closing tags are there). But Access does not display it anymore correctly (bold, yellow, bullet list, etc.)

On the other hand,those records that I worked with just recently are still nicely formatted in RTF as they should.

The question

I need advice what could I do in order to get Access recognize the html correctly and use it for displaying graphic features. Some form of string operations? Some form of re-import?

Documentation

Here is an example of one such a record that does not get displayed correctly:

Important detail: what they call "RTF" is actually HTML

Note that I did everything exclusively in MS Access 2010. All the markup is set by me personally (there is no imported markup), and all this markup is actually HTML. However, setting the table field and form field properties for the use of HTML means setting them to what they still call "RTF" there. Obviously they did not bother to rename it. From all what I know, no "real" RTF is involved, it is all HTML.

Recent changes

I have no idea what caused this. I have lately done a lot of operations, especially importing another 1000 records from an external source, and some additional work in the VBA programming of the database. I don't think the import or the VBA programming has caused he problenm.

A possible culprit

However, one thing that might well be related is: during my various recent activities in the database I recognized that in the table that holds all the records (t01_main) the one field that should have formatted text (fiedl named [SummaryRTF]) was not set to "RTF" (see note above; means HTML) but to "plain text".

Strangely this so far had not been a problem. The form field that displays the text and where I do the formatting was set to "RTF" (i.e. HTML) anyway. I had no problem with the formatting at that time.

I then adjusted the table field property to "RTF" (read: HTML).

I strongly suppose this is what now causes the problem. I suppose, certain changes have been made in the text during that step.

So again the question

The question is: what can I now do to restore my nice formatting, based on the html that is obviously still kept in the field?

Perhaps good to know

• I have backups of the phases of my work and could, if that helps, import from there older "healthy" data - (from the table field with property: plain) into the same field in the latest version of my table (with the now "bad" data, where the same field is "rtf")
• I can work with VBA to do string operations or whatever, or do SQL queries through VBA
• But of course, the quicker the solution, the better

Addenda

1) I now checked in a backup version of the database (with the field in the table set as "plain text"). Here, in table view, the text has also html tags; however, in the form field (set as "RTF") it displays nicely formatted (bold, yellow, etc.)

2) I checked - in a backup version - what happens when you change the tabled field setting from "Text format: plain text" to "Text format: RTF". Access 2010 then displays an alert as follows:

Solution

The solution is simpler than one would believe.

MS Access has a built-in tool to fix the problem. As in the original question visible under "Addenda" there is a function called "PlainText" that can be used, via an update query, to remove the HMTL tags.

Now, this alone would not be helpful at all because I do not need plain text; the valuable thing was all my careful formating. However, strangely, it does more, namely restoring the old formatting.

So, what I did is I run an update query

UPDATE t01_main SET t01_main.SummaryTEST = PlainText([SummaryRTF]);

to fill a new field [SummaryTEST]. (I used a new field rather than the source field just to be sure I could reverse it if anything went wrong).

Now the formatting is nice again.

But attention! If some of your fields you are mingling through the "PlainText()" mill are already nice and beautiful, the function would remove your formating.

In may case it meant I had to exclude from the update query all the records that were still okay, i.e. those I had formated just today after I had set the table field property from "plain" to "RTF". Happily all those were just from last year 2017, so I could simply exclude them by:

UPDATE t01_main SET t01_main.SummaryRTF = PlainText([SummaryRTF]) WHERE (((t01_main.Year)<>"2017"));

Comments

[Yorik]

RTF is very different from HTML. I suspect the HTML has been pasted in (or imported from other sources, as you say) rather than applied using any type formatting gadgets. for example, Bold is <b></b> in html but \b\b0 in rtf format.

[Christian Geiselmann]

@Yorik. Important here! I did all this exclusively in MS Access 2010, and all the formatting there is actually HTML, although it is called "RTF" in the field properties. But there is no actual "RTF" (as in your example), it is, I repead, all (!) pure (!) HTML (!) both in the older and in the newer records. - I do not import rtf text; I import only plain text.

[AFH]

Sorry, my edit to make your image in-line may have crossed with your latest update. I have looked at the differences, but I find it difficult to work out if I previously reverted your previous update.

[Yorik]

Then it is not RTF, you should alter your tags. By the way, did you use a query etc? Queries sometimes mangle field types

[Christian Geiselmann]

@Yorik Yes, you are right. I will do this right now. I was misled by this strange terminology they use.

[Christian Geiselmann]

@Yorik: Use of queries: queries are involved all the time. The field content runs through a query to get displayed in two different forms and one report. - During normal work, I do a lot of filtering in my main form (usually with "filter by form"). Filtering is the point of the entire database.