vba IF Like with spaces

[danuk1976]

Hi all

Does anybody know the symbol for space when doing a like or am I missing something

this does not work

If Me.TextBox5.Text Like "*At At*" Then        
Me.TextBox5.ForeColor = vbRed
        Else
        Me.TextBox5.ForeColor = vbBlack
    End If

I also have this below looking for a match with ' character (eg brian's)

it works good, so need the character code for a space

If Me.TextBox5.Text Like "*ƒ??*" Then       
Me.TextBox5.ForeColor = vbRed
        Else
        Me.TextBox5.ForeColor = vbBlack
    End If

 

[Rick Rothstein]

Does anybody know the symbol for space when doing a like or am I missing something

this does not work

If Me.TextBox5.Text Like "*At At*" Then        
...
...

There is not special character for a space... a space is a space. As written, your If..Then line of code above is checking TextBox5 for any number of unspecified characters followed by an uppercase "A" followed by a lowercase "t" followed by a (normal) space character followed by an uppercase "A" followed by a lowercase "t" followed by any number of unspecified characters. Notice the uppercase and lowercase as that is important... if either of those A's are lowercase, or either of those t's are uppercase, the test will fail. Also, if TextBox5 contains two spaces between the two "At" text, the test will also fail.

I also have this below looking for a match with ' character (eg brian's)

it works good, so need the character code for a space

If Me.TextBox5.Text Like "*ƒ??*" Then       
...
...

Just pointing out that the above If..Then test is not testing for an apostrophe (') at all... those question marks will allow any characters to exist at the two characters after the lowercase "f". If you want to test for an apostrophe directly, then put an apostrophe in at that location.

 

[danuk1976]

thank you kind sir, running this vba in publisher but don't think that should make a difference

I will try this again over the weekend and give some feedback on where I am going wrong

 

[danuk1976]

okay schoolboy error, i was running 2 instances as below so yes "At At" was coloured red but if "As As" was false it quickly coloured black

[COLOR=#574123]If Me.TextBox5.Text Like "*At At*" Then        
[/COLOR]Me.TextBox5.ForeColor = vbRed
Else
Me.TextBox5.ForeColor = vbBlack [COLOR=#574123]    
End If[/COLOR]

[COLOR=#574123]If Me.TextBox5.Text Like "*As As*" Then        
[/COLOR]Me.TextBox5.ForeColor = vbRed
Else
Me.TextBox5.ForeColor = vbBlack 
[FONT=Verdana]End If[/FONT]  [COLOR=#574123]
[/COLOR]

thanks again for confirmation rick