sql in vba doesn't work...resulting string does work in query builder (1 Viewer)

result of debug.print strsqlim2...
SELECT tempq1.CLLI, tempq1.alarm_desc, Sum(tempq1.SumOfCountOfmicro) AS SumOfSumOfCountOfmicro FROM tempq1 GROUP BY tempq1.CLLI, tempq1.alarm_desc HAVING (((tempq1.CLLI)='MTRLPQ01') AND ((tempq1.alarm_desc) like "*host to m*"))

If I copy and paste the resulting string in a new query (sql view) the records come up properly. When I run in VBA, no records are found.

The problem appears to be the like operator at the end... if I use = instead of like, the vba sql string pulls records okay. I need to use the like operand though.

I'm thinking either the cursor type or lock type might be affecting, but don't know enough about that to know for sure.

Thanks in advance for any help with this one.

Al

Found the answer in another post:

DAO uses * as a wildcard, ADO (and SQL server) uses % as a wildcard. I didn't want to have to go through ever darn module everywhere and change * to a % so I've used aLike with % which should (so I'm hoping) stop that problem, I think.

I changed my code to use the % and voila. Been beating myself up with this for hours.

code version:
".....alarm_desc HAVING (((tempq1.CLLI)='" & strclli & "') AND ((tempq1.alarm_desc) like 'host to m*'))"

there is a difference in code between concatenating 'string-literals' and concatenating '" & variables/controls holding string values & "' into your strSQL

also ......" & "'" &
is a waste of clock cycles - string concatenation is not the fastest thing you can do.

izy

yes: %/* needs to be right
but your concatenation was also wrong

izy