Rich Text Format in Access Field to Report

[willowdom]

hi Guys, I have the following Problem, I imported a table from excel to access, the table have a Field name NOTE, that field have a bunch of Notes(strings) but the notes came originally from the Original Database(ACT!) with rtf format and I need to take everything out and only get the PlainText but until now, nothing works, here is a sample of the original data: {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl {\f0\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\f0\fs17 Cancelled Opportunity. Was not a valid opp. Never went anywhere.\par
}



I tried using Application.PlainText in access and nothing happen, the only way to see the data correctly is Telling the design table that the NOTE field is a Rich Text and Access automatically eliminate the garbage but when I want to show this info in a textbox, the textbox only show the data when you setfocus over him, if is not, the textbox looks empty, if I run a report to show the data is the same problem and takes toooooooooo long to show the report, how can I convert this richtext to plaintext?

Thanks!.

[CJ_London]

depends on how similar other values are, but assuming the basis is

{formatting code, space, text required, format code} you can use instr, left, right and mid functions etc to remove the formatting code - perhaps something like

strip off codes to right
left(note, instrrev(note,"\")-1)

with this strip off codes to left
mid(left(note, instrrev(note,"\")-1), instrrev(left(note, instrrev(note,"\")-1),"\")+1)

now strip off remaining bit of code to left (i.e. before first space)
mid(mid(left(note, instrrev(note,"\")-1), instrrev(left(note, instrrev(note,"\")-1),"\")+1),instr(mid(left(note, instrrev(note,"\")-1), instrrev(left(note, instrrev(note,"\")-1),"\")+1)," ")+1)

edited - code lost the \ when saved, hopefully stays this time

nope - \ gone again, try with code tags

[isladogs]

I know you say you've tried Application.PlainText but the following may solve it without needing to parse it manually.
I've never had to go through the hoops that you're now facing

Create a query based on your imported table. Add a field PlainText(YourRichTextField).
If that works, you could then convert to an update query and fix the field.

Or re-import with an import spec to handle that
Or create an unformatted copy of the field in the source db before importing

[June7]

I can't get Access to recognize that string as RTF. Perhaps you should provide the Excel workbook for analysis if still need help.

[Micron]

I don't know what "I can't get Access to recognize that string as RTF" means because it works for me if the string is in a table and the field is long text formatted as RTF. However, in a query PlainText([Field1]) returns

\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl {\f0\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\f0\fs17 Cancelled Opportunity. Was not a valid opp. Never went anywhere.\par
}

so maybe that's what was meant?

Spent hours researching and trying to figure out why PlainText function would not work in a query and examined LOTS of suggestions that didn't work, including RegExp. In playing around, I could remove parts of the rtf tags, but was never left with the desired outcome.

You won't believe what I hit on by myself - a ONE line function which might work in this case because there is a form textbox involved, but it depends on whether or not an unbound control is OK. If it's only for viewing, then to test this outside of a custom function like what I wrote, in the form
- hide the bound textbox e.g. Text0
- add a 2nd textbox for viewing and make its control source =PlainText(Text0.Text)
The magic is in the .Text property. Leave it off and it won't work.

The one line function (if there is a form control a query can reference)

Public Function stripRTF(strInput As String) As String
stripRTF = PlainText(Forms!frmRTF.Text0.Text)
End Function

This could negate the need for updating an existing field, which didn't work for me anyway, unless it used the function and could refer to a form control.
I suppose all this will be for naught if the form is continuous or datasheet type.

[isladogs]

Micron
I'm also confused reading your answer. PlainText([FieldName]) normally works fine in a query
For example, see https://www.access-programmers.co.uk...61&postcount=4

However, I don't recognise this as 'standard' rich text which is of course a limited subset of HTML.
Perhaps the ACT 'dialect' of rich text is different to the MS version?

[CJ_London]

my solution works - on the proviso as stated

[CJ_London]

Spent hours researching and trying to figure out why PlainText function would not work in a query

perhsaps a simple as plaintext in a .accdb is set to stip out html whilst in a .mdb it strips out rtf codes

[Micron]

I'm also confused reading your answer. PlainText([FieldName]) normally works fine in a query

Well, I'm saying it didn't and I posted the query field expression (minus the alias part) and the result, so I don't know how else to phrase it. Possibly it's a version issue, or perhaps it's the tags being used in the source. I'm no rtf expert, but the sample text doesn't look much like the html that I learned. Be that as it may, my version of Access can correctly parse the sample correctly when the field is long text and formatted as Rich Text - again, that's my experience with version 2016. Also, it does exhibit the behaviour of not being visible when the field isn't the active field.

To me, the link you posted doesn't reflect an example of the type of tags this post is dealing with. The only thing I can say is that PlainText function in a query didn't work for me. Nor did it in vba code. However, the function did work on the .Text property of a control or field, but not on its .Value property which I didn't explicitly use since it was the default.

The summation of all this is that for me, one simple code line did the trick without trying to strip out all the unwanted stuff using a find/replace approach. As for a query using the PlainText function, the same simple approach worked as a calculated field as long as I had a form control to refer to.

[isladogs]

Hi Micron
My answer wasn't as anywhere near as clear as I intended but I think we're making similar points.

For 'standard' RTF, using PlainText in a query will work
It doesn't in this case because it's not standard RTF.
Between us we've tested in 2010 & 2016 so fairly confident it's not a version issue.

Anyhow I'll steal your function. Thanks
What confused/surprised me was that it worked using your one line function as it's effectively the same thing

[Micron]

What confused/surprised me was that it worked using your one line function as it's effectively the same thing

The caveat being that I could only get it to work on the .Text property, which AFAIK is only available to a control and not available on a table/query field. To reiterate, the PlainText function had to be applied against the .Text property, which makes sense to me. If I used it on the .Value property (either explicitly or by default) the result was the same, i.e. it didn't produce the desired result.

Feel free to steal from me whatever you like. You will probably not be so inclined very often.

[June7]

I am confused. Notation in that string is not HTML tags so Access does not format. Therefore, of course PlainText function does not work.

Expression in query or textbox =PlainText([fieldname]) works on text I input into field formatted by the toolbar or even by VBA. Other expressions in texbox =PlainText([controlname]) and =PlainText([controlname].[Text]) have same result. The one-line custom function using the intrinsic function referencing the strInput argument produces same. Referencing control and its Text property in the one-liner just triggers "control must have focus" error. Set form so that control will have focus when form opens and still does not extract desired text from that string. However, the latter does return value associated with each record - not the same value in all records - on continuous/datasheet form.

[isladogs]

The caveat being that I could only get it to work on the .Text property, which AFAIK is only available to a control and not available on a table/query field. To reiterate, the PlainText function had to be applied against the .Text property, which makes sense to me. If I used it on the .Value property (either explicitly or by default) the result was the same, i.e. it didn't produce the desired result.

Feel free to steal from me whatever you like. You will probably not be so inclined very often.

LOL!
I believe the .Value property is only available if a value is saved. Therefore your use of .Text makes total sense.
I haven't tried it on this text string yet.

[Micron]

I am confused. Notation in that string is not HTML tags

Correct. It isn't recognized as any form of markup language, nor is it an RFC language. It's a document file format, one which I wouldn't be surprised to see going the way of BASIC and dinosaurs, and being replaced by xml.

[June7]

My confusion comes from the statements:

"one simple code line did the trick without trying to strip out all the unwanted stuff using a find/replace approach"

and

"I could only get it to work on the .Text property,"

and

"The caveat being that I could only get it to work on the .Text property,"

None of suggested approaches would extract the desired text from the posted sample for me.