With native Microsoft Access tables, specifying a criterion in Query Designer as Like [some parameter] & "*" is no problem. I am prompted for the parameter, the asterisk is appended to the end of whatever I enter at the prompt as pattern to match, and I get a dataset satisfying the criterion.
When I try to do the same thing with linked ODBC tables (connected to Microsoft SQL Server via SQL Server driver), Access just hangs up until I kill the process. I can pass the Like operator with a string pattern (Like "some pattern*") or the result of a parameter prompt (oddly, in Query Designer criterion cell, I have to express it this way: "" & [some parameter] & ""), but I cannot figure out how to pass the like operator with pattern and parameter together, as is possible with native Access tables (as shown above). I have tried different combinations of CStr function, Chr function, parentheses to influence order of operations, doubling up single quotes, double quotes, square brackets, etc.
To give an idea of what does and doesn't work, below is some of the things I have tried, with [lot] being the Access parameter. The first character is "o" for success (at least partial success), and "x" if the option did not work at all. From the results, I don't see a way to make it work.
xLike [lot] & "*"
xLike [lot]
oLike "*"
o[lot] & "*" empty result
o"*" Access changes this immediately to Like "*"
x[lot]
o"""" & [lot] & """" empty result
o""" & [lot] & """ empty result, no prompt for parameter
o"" & [lot] & "" shows data for lot entered
xLike "" & [lot] & ""
xLike """" & [lot] & """"
oLike "123456" shows data for lot entered
oLike 123456 Access changes this immediately to Like "123456"
xLike ([lot] & "*")
xLike CStr([lot] & "*")
xLike ([lot])
xLike CStr([lot])
oLike ("*")
oLike CStr("*")
oLike (*) Access changes this immediately to Like ("*")
oLike CStr(*) Access changes this immediately to Like CStr("*")
oLike * Access changes this immediately to Like "*"
xLike [lot] & *
oLike * Access changes this immediately to Like "*"
o* Access changes this immediately to Like "*"
o[lot] & * Access changes this immediately to [lot] & "*" empty result
xLike ([lot] & *)
xLike (CStr([lot] & "*"))
xLike CStr([lot]) & "*"
xLike CStr([lot]) & * Access changes this immediately to Like CStr([lot]) & "*"
xLike (CStr([lot]) & "*")
xLike (CStr([lot]) & *)
xLike ([lot]) & ("*")
xLike ([lot]) & (*)
xLike CStr([lot]) & CStr("*")
xLike CStr([lot]) & CStr(*)
x(Like ([lot]) & ("*"))
Can anyone tell me how the criterion string should be expressed in query designer, so that WHERE clause gets passed to SQL Server in the same way that Like [some parameter] & "*" gets passed to native Access tables? Even a tool to show what's getting passed to SQL Server would help. I am using Access 2016, SQL Server 2014, and driver SQLSRV32.dll version 10.00.10240.16384. I do not have the option of using other drivers, like SQL Server Native Client.
It turns out that there was no incompatibility between Like [some parameter] & "*" and the SQL Server or the SQL Server driver used. It was only that the query was very inefficient in its design but caused no problem until after migrating to SQL Server back end. I had one table with three left outer joins to three other tables and a where clause predicate on a column of one of the right tables (on a text column). Creating indexes on the columns involved in the join and where clause predicates did not help. What solved the problem was merely changing the left join to the table with where clause predicate to an inner join (since, effectively, that's what the where clause does to that particular relationship). After this change, the resulting data set is returned nearly instantly.