VBA script using contains not working

Question

Trying to do a simple VBA script to look at a field and if that field contains 99 in the middle then show another field. I can get a normal statement to work i.e. field2 = "5D992" but not a contains statement using wild cards (see script below). I think it might be just my syntax is wrong, any ideas anyone??

Sub OnFormat

    IF rpt.field3.Text.contains "*99*" THEN 
        rpt.field2.visible = false
    Else 
        rpt.field2.Visible = true
    End If

End Sub

Thanks for any suggestions.

Smittey · Accepted Answer · 2014-11-14 11:46:27Z

1

You should be using InStr.

If InStr(1, rpt.field3.Text, "99", vbTextCompare) Then 
   rpt.field2.visible = false 
Else 
    rpt.field2.visible = true 
End If

See MSDN InStr Function documentation for more information

answered Nov 14, 2014 at 11:46

Smittey

2,49010 gold badges29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rebecca Bailey Over a year ago

Thanks for this Smittey, but are wildcards not used for this function??

Smittey Over a year ago

You're welcome! If I understand the question correctly, do you mean using a wildcard character (e.g. '*')? If so, this InStr function will look for 99 in the string supplied to the second argument (rpt.field3.Text) so effectively it is using wildcards, so "*99*"!

Rebecca Bailey Over a year ago

Great thanks for the explanation, much appreciated. The script works wonderful now, much appreciated.

Collectives™ on Stack Overflow

VBA script using contains not working

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related