Multiple String Replace

[TiggerNYC]

Hey...I am an access developer...but not with a lot of experience with vba.

My database contains a tblMain which has a Comment field. This field is a memo type. I need to be able to replace various strings within the memo field for reporting. i.e. someone requests the search in the comment field for "process" or "technology" or "manager"...

I am using a QBF for the requested criteria to generate the report and would like to make the strings stand out in the report...i.e.

Replace(Reports!rpttest![OPEN1], Reports!rpttest!text3, StrConv(Reports!rpttest!text3, 1)) ...using uppercase in this instance (unless someone has a solution for bold or italic or?)...

I don't want to permanently change the tblMain.Comments field, so that the tblMain data will be unformatted for the next query/report.

Any ideas on how to use vba to handle mutliple instances of this replace function? and have it display in the report...

TiggerNYC

 

[dcx693]

Any ideas on how to use vba to handle mutliple instances of this replace function?

You should use the Replace function in a calculated field in a query to feed into your report. The Replace function by default will change all instances of the found string in each field.

By the way, you can also use the UCase() function to change your string to upper case.

 

[TiggerNYC]

Thanks for the reply. And I can use the function in the report...but how do I handle multiple replaces of the same field?

Thanks for the UCase tip...

 

[dcx693]

but how do I handle multiple replaces of the same field?

Unless you specify otherwise, the Replace function will replace all instances of a found string with the replacement string in your field. So that:
UCase(Replace("aaba","a","b"))
will yield
"BBBB"

Is that what you meant?

 

[TiggerNYC]

Sorry that I was unclear...the user will specify any number of search terms...i.e. "process", "technology","career"

I need a function that will search the memo field for any/all of those terms and change them to upper case (or other format) to stand out on the report. I don't want to update the actual table, as I want it "clean" for the next search...

Does that clear it up a little?? (clear as mud? )

 

[dcx693]

You can do this in a query using nested calls to the Replace function like:
Replace(Replace("High, Low","High","HIGH"),"Low","LOW")

That expression will replace all instances of "Low" with "LOW" and all instances of "High" with "HIGH".

 

[TiggerNYC]

I was having a brain fart with the nesting of the replace function...I will give it a try...

The user may have up to 15 search words...will it be over complicated to nest 15 replace functions?

 

[dcx693]

I've never tried it, so I can't say. The only thing I can say is: try it and see.

 

[TiggerNYC]

I successfully nested 15 replace functions...no noticible slow down in generating the report.

Thanks.