Replace text case sensitive

[jsic1210]

Hello,
I'm trying to open a report and highlight certain text. So I have a textbox that takes the original field and replaces certain text (based on user input) in bold red. Here is my formula:

=Replace([Language],[txtKeyword],"<font color = ""red""><b>" & Upper([txtKeyword]) & "</b></font>")

It works fine, but the only problem is, I don't know how to make it case sensitive. So let's say the user input (txtKeyword) is "contract." If it's the beginning of a sentence, I want the C to remain capitalized, but if the C is not capitalized, I don't want it to show capitalized. Is there a way to do this without making the formula really long?
Thanks!

 

[pr2-eugin]

How about,

= Replace([Language], [txtKeyword], "<font color = ""red""><b>" & [B][URL="http://www.techonthenet.com/access/functions/string/strconv.php"]StrConv[/URL]([/B][txtKeyword], [B]vbProperCase)[/B] & "</b></font>")

 

[jsic1210]

Thanks for the quick response. Since I'm highlighting parts of strings (and not the whole textbox), the first letter is not necessarily going to be capitalized. I'm not sure this code will work for what I want to do.

 

[pr2-eugin]

Can you give an example scenario?

 

[jsic1210]

Users open a form, looking for any contract with the phrase "tax code." A report comes up, listing all contracts with "tax code," in the field [Language], and highlights that phrase. So one contract's Language field might say "Tax code has been updated in 2014." Another contract's Language field might say "There will be a new tax code in 2014." For the first one, I'd like the "T" to remain capitalized, but in the second, I don't.

This really isn't a make it/break it thing, I could always just capitalize the whole phrase.

 

[pr2-eugin]

So in other words, you are not looking to do any replace, but just add font tags around the key word? In this case "tax year"?

 

[jsic1210]

Right. I don't want the actual phrase changed, just the font of the phrase.

 

[pr2-eugin]

How about this function then?

Public Function SpecialReplace(Expression As String, Find As String) As String
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim startPos As Long, endPos As Long, resultStr As String
    startPos = InStr(Expression, Find)
    endPos = Len(Find)
    
    If startPos <> 0 Then
        resultStr = Mid(Expression, 1, startPos - 1) & "<font color = 'red'><b>" & _
                    Mid(Expression, startPos, endPos) & "</b></font>" & _
                    Mid(Expression, startPos + endPos)
    Else
        resultStr = Expression
    End If
    SpecialReplace = resultStr
End Function

I randomly tested,

? SpecialReplace("Bill has been updated in 2014.", "tax code")
Bill has been updated in 2014.

? SpecialReplace("Tax code has been updated in 2014.", "tax code")
<font color = 'red'><b>Tax code</b></font> has been updated in 2014.

? SpecialReplace("", "tax code")


? SpecialReplace("There will be a new tax code in 2014.", "tax code")
There will be a new <font color = 'red'><b>tax code</b></font> in 2014.