DataView.Filter not working with multiple % in "like %"?

Question

Below SQL query works when the spaces in the role name are replaced by %: 1 row is returned

select * from cmrdata.dbo.tblRoles where rolename like '%Super%Administrator%'

However, when I try to mimic the same in DataView.RowFilter, it does not return any rows.

dv.RowFilter = "RoleName like '[%]" & Replace(roleName, " ", "[%]") & "[%]'"

I also tried without the [] around the %. Please advise.
Thanks for your help in advance.

Answer 1

Error in Like operator: the string pattern '%super%administrator%' is invalid.

Why are you replacing spaces with a %? What type of rows are you trying to match?

Answer 2

Kindly suggest using

WHERE role_name like "%SUPER%" 
  AND role_name like "%ADMINISTRATOR%"

or

WHERE role_name like "%SUPER ADMINISTRATOR%"

Don't forget; regular expressions are always there for you if criterion gets to complex.

Cheers.