Solved correct query syntax - (re making the found word appear red colored in report)

[EdK]

1. This should be simple in theory and I am half way there (I think) but just can't crack it
2. I will show various pics to help make it clear
3. The basic elements I think I have got right, but I need to know the exact syntax (in context with the whole query expression) for making the found word look red instead of black
4. The word being searched for is "adventure", I entered that into Form "formfindword" unbound text box called "Text0" (pic is at 5.)
5. 
6. The report containing the found word is called "Report foundit2youbeauty" - you can see the red bit below, it is the wrong result but in the right place, where the word adventure was -
7. 
8. The query that the report is based on (and in which the "Replace" expression syntax is doubtful, but hopefully nearly there) is called "tempbooktext2 Query"
9. The current expression in that query, that looks for and finds the correct spot for every occurrence of the word in field Text0, and/but shows a (wrong) result in red text, in all the correct spots of the printed report, is --
10. Expr1: Replace([booktext2],[forms]![Formfindword]![Text0],"<font color=red>[booktext2],[forms]![Formfindword]![Text0],</font>")
11. Can someone please tell me the whole exact Expr1 that should work, please?
12. I am Access illiterate, by the way

EdK

 

[Gasman]

If you are doing a replace then I would be quoting the text to be replaced?
So Replace("string to search","string to find","string to replace with")

However I would likely just concatenate that text in with the rest

Try

Expr1: Replace([booktext2],"[forms]![Formfindword]![Text0]","<font color=red>" & [forms]![Formfindword]![Text0] & "</font>")

Edit: Removed second [booktext2]

 

[CJ_London]

Don’t think you need quotes around the 2nd parameter

 

[arnelgp]

do not use Replace, create a UDF that will replace the string.
if you use Replace, the Text will be replaced of whatever the Case of the replacing word.
in your sample, if Text0 is all in lowercase and the original text is in Uppercase, the
word will be replaced with lowercase, which disturbs the original casing of the Text.
Not acceptable if the word is a Surname of any Formal name.

 

[EdK]

Don’t think you need quotes around the 2nd parameter

Hi CJ London to short circuit things (I am confused due to Access inexperience, and age - coming 81 yrs) would you be able to show me what the whole exact expression would be, please. ie (and this is luddite of me) I don't really want to understand why etc, I just want to put an expression into the query that works. I feel I have got close, but no cigar. It is just a tad frustrating .... I tried Gasman's suggestion, it did not work, I think on inspection, there might be two commas in it that should be full stops. Then I tried to understand your comment, and fiddled around, then gave up. I have been giving this a shot every few years or so, and have wasted so much of my stem cells, I'm really hoping you can get me across the line, and then I can go finish my WIP book EdK

 

[EdK]

do not use Replace, create a UDF that will replace the string.
if you use Replace, the Text will be replaced of whatever the Case of the replacing word.
in your sample, if Text0 is all in lowercase and the original text is in Uppercase, the
word will be replaced with lowercase, which disturbs the original casing of the Text.
Not acceptable if the word is a Surname of any Formal name.

Thanks arnelgp. I take your point. In my case, it (being able to quickly highlight the red word from various spots in my database and/or book records, from the print preview screen view, whilst eg editing by hand a separate hard copy) will mostly be what I'm doing. And that won't affect the actual records, as the query is only to produce that print preview on screen. Hope that makes sense. EdK

 

[arnelgp]

post #2 is correct but need to remove the quote (post #3):

Expr1: Replace([booktext2],[forms]![Formfindword]![Text0],"<font color=red>" & [forms]![Formfindword]![Text0] & "</font>")

 

[EdK]

post #2 is correct but need to remove the quote (post #3):

Expr1: Replace([booktext2],[forms]![Formfindword]![Text0],"<font color=red>" & [forms]![Formfindword]![Text0] & "</font>")

Thanks a million, arnelgp, that works perfectly !!!! Is there some button on the forum that I can click to show my appreciation? EdK

 

[Gasman]

Like icon?
Bottom right of each post. Can also mark it solved. Top right of thread.

 

[EdK]

Like icon?
Bottom right of each post. Can also mark it solved. Top right of thread.

Thank you, Gasman !!! EdK