Like operator not working

[cliftonx]

Added the following Like filtering pattern:

ssql = ssql + " and Chemicals.[Chemical Name] like '" & txtChemicalName.value & "*' "

The code works as expected. However when I try to make the following modification to append an asterisk to the beginning of the like operator:

ssql = ssgl + " and Chemicals.[Chemical Name] like '*" &
txtChemicalName.value & "*' "

I get the following error; "Error Search Criteria". I can manually enter the command on the Sql Server side without any problems. But can't figure out why appending an asterisk at the beginning to the like operator I get an error on the frontend VBA code. Please Advise?

Thanks,
Clifton.

 

[CJ_London]

not sure if it is relevant but sql server uses % rather than *. Also if this is being done using ado then you need to use %. And I seem to recall ado doesn't allow an initial */%, but happy to be corrected.

 

[cliftonx]

Yes, I'm using ADO and tried using the % as a wildcard but I get the "Error search criteria" error message. However it does work with the * wildcard, but only when I use it at the end of the of the search string i.e. Like "A*" but not Like "*A".

 

[CJ_London]

regret I am out of ideas - have you tried using dao instead - it is generally faster and more flexible