Spaces in On Change event Search Box and Blank Row

[Pianopizza5]

Thanks for attaching it. I'm just super wary of downloading files, from anyone.

(I realize that I've been attaching files myself.)

[Pianopizza5]

Good day,

What would you suggest using instead of the Replace function? Wracking my brains on this one. Did you happen to write something out? Appreciate any guidance.

[CJ_London]

to what purpose - why not use the replace function?

[Pianopizza5]

The problem that I am running into is keeping the text cases correct (I already tried using the vbBinaryCompare in the compare argument).

Currently, if you search “a,” it replaces all “a” and “A” with a lowercase “a.” However, I want the txtToSearch to “maintain” its case.

The vbBinaryCompare argument takes it halfway there. However, if you search for “a,” only the lower case “a”s will be highlighted.

Let me know if that makes sense. I can clarify.

[Pianopizza5]

Basically, I want the characters typed in the search to turn red in the search results. It’s almost like I want to disregard the case of the text in the search.

Ex. “a” brings up the results “Apple”, “Test a”

[CJ_London]

characters typed in the search to turn red in the search results

OK so you want to highlight 'A' but not as 'a' - so you are using a richtext field

The way I have done that in the past is to find the actual characters using the mid and instr functions. The problem is you need to start with a 'plaintext' version then insert the formatting code and then return back to rich text

strSearch = "abc"

strText "It's as simple as ABC"

strTemp=plaintext(strText) 'to remove any formatting

dim phrase as string

strTemp=plaintext(strText) 'to remove any formatting
strPhrase=mid(strTemp,instr(strTemp,strSearch),len(strSearch))
strTemp=lreplace(strTemp,strPhrase,"<font....>" & strPhrase & "</font>",,1)
strtext=replace(strTemp,vbcrlf,"<br>")

that only replaces the first instance - you can use the instr+length of strSearch to set the replace start position to progress through strTemp so you are covered for Abc, aBc etc

Technically you can just used this method on the richtext value, however it runs the risk of modifying existing html code (e.g. you are searching for 'font') which will just blow the whole thing up

[jojowhite]

dont be to wary, I the db came from you, remember.
I just updated it.

[Pianopizza5]

Thanks! I tried out the code and am getting the run-time error code 5 on the strPhrase line (I had to declare some of the variables.)

Function HighTest(strText As Variant, strSearch As String) As VariantDim strTemp As StringDim strPhrase As StringConst strcTagStart = "<font color=red>"Const strcTagEnd = "</font>"strTemp = PlainText(strText) 'to remove any formattingstrPhrase = Mid(strTemp, InStr(strTemp, strSearch), Len(strSearch))strTemp = Replace(strTemp, strPhrase, strcTagStart & strPhrase & strcTagEnd, , 1)strText = Replace(strTemp, vbCrLf, "<br>")End Function

[Pianopizza5]

Function HighTest(strText As Variant, strSearch As String) As Variant
 
Dim strTemp As String
Dim strPhrase As String
 
Const strcTagStart = "<font color=red>"
Const strcTagEnd = "</font>"
 
 
strTemp = PlainText(strText) 'to remove any formatting
strPhrase = Mid(strTemp, InStr(strTemp, strSearch), Len(strSearch))
strTemp = Replace(strTemp, strPhrase, strcTagStart & strPhrase & strcTagEnd, , 1)
strText = Replace(strTemp, vbCrLf, "<br>")
 
 
End Function

[Pianopizza5]

Dang, I really want to see your edits, but I am unable to open it.

[Welshgasman]

Thanks! I tried out the code and am getting the run-time error code 5 on the strPhrase line (I had to declare some of the variables.)

Function HighTest(strText As Variant, strSearch As String) As VariantDim strTemp As StringDim strPhrase As StringConst strcTagStart = "<font color=red>"Const strcTagEnd = "</font>"strTemp = PlainText(strText) 'to remove any formattingstrPhrase = Mid(strTemp, InStr(strTemp, strSearch), Len(strSearch))strTemp = Replace(strTemp, strPhrase, strcTagStart & strPhrase & strcTagEnd, , 1)strText = Replace(strTemp, vbCrLf, "<br>")End Function

Works for me, in that it runs.

? hightest("Always","ways")
Al<font color=red>ways</font>

[Pianopizza5]

SearchWSpacesExampleStrCode.accdb.zip

Line 11: strPhrase = Mid...

[Welshgasman]

Show what you are passing in.
Plus learn to walk your code with F8 and breakpoints.

[CJ_London]

Perhaps strsearch is zero length? And why declare strtext as variant?

[CJ_London]

just realised this is the onchange event - so the control will not have been updated, You need to use strSearch.text