Conditional formatting syntax error MS Access 2007

Question

Hello there. I hope you can help me with a simple answer, to do what I would think is a very simple thing.

I use the following expression in a query, which is:

Like "*" & [forms]![Formfindword]![Text0] & "*"

to find any text field in my database (Access 2007) that contains the word or phrase I want to find. The resulting report works perfectly. It shows only those text fields in the database that DO contain the word or words I was looking for.

Now, as an improvement, I want my report to highlight, with colour, NOT the whole text field (which may have half a page or more of text in it), just the word or words that I searched for. That should be simple enough!

However, I am unable to work out what expression to use in the report's layout view, conditional format process, and it comes up with the error message "The expression you used contains invalid syntax".

The expression I AM using in the conditional formatting process is:

Like [forms]![Formfindword]![Text0]

which is my "best guess" ....

What is the correct expression to use? Or is there a better way to achieve what I want?

I know nothing of SQL or VBA or whatever it is called, unfortunately.

Any help is appreciated and I sure hope you can. Thanks in advance.

EdKerm

By the way, this is in addition to a post/question I did about 5 days ago (?3 July, 2013 perhaps) and which nobody had yet answered, so I thought I would put it to the forum again, in simpler terms.

Answer 1

Quote:  "There is no built in or simple way to do that.  Formatting only part of a string requires you to use Rich text or Hypertext (or is it XML), which in turn means you need to insert the formatting commands into the text.  This can be done using VBA code, but you have to decide which style you will use and then figure out the formatting commands you want to use, much like you would if you were creating a web page."

Thanks Marshall.  I was afraid VBA might have to be the case.  Yes, it IS rich text format we are talking about.

So, then my question to the wider forum has to be asked to VBA gurus:

what VBA code/routine will insert a simple formatting command (changing to, let's say, light blue) that part of the string that has been searched for/found)?

Come on, VBA gurus, we (my laptop and I) know you're "out there", we've seen amazing code stuff galore elsewhere in this fantastic forum.

Thanks for your interest, in advance

EdKerm

Answer 2

There is no built in or simple way to do that.  Formatting only part of a string requires you to use Rich text or Hypertext (or is it XML), which in turn means you need to insert the formatting commands into the text.  This can be done using VBA code, but you have to decide which style you will use and then figure out the formatting commands you want to use, much like you would if you were creating a web page.