Problem with RecordSet and "LIKE" Operator

[haris101]

Hi,

I'm a newbie developing an Excel VBA project. I have a problem when trying to retrieve some data stored in Access database. Below are several lines of my code.


strCmd = "SELECT id_platform, name FROM platform WHERE trim(lcase(name)) LIKE '*" & Trim(LCase(strName)) & "*'"

rst.Open strCmd, con, adOpenKeyset, adLockOptimistic

If rst.RecordCount = 1 Then
...
ElseIf rst.RecordCount = 0 Then
...
End If


When the query above is executed, the Recordset.RecordCount is always 0. However, when I ran the query command above in MS Access (by copying the value of strCmd from VBE's Watch Window), I got several rows. Furthermore, I've also tried to remove the " WHERE ..." part and I got several rows. I also get several rows when I use "=" operator instead of "LIKE".

What could be the problem?

Many thanks in advance,

Haris

[Bob Phillips]

Try



strCmd = "SELECT id_platform, name FROM platform WHERE " & trim(lcase(name)) & " LIKE '*" & Trim(LCase(strName)) & "*'"

[haris101]

Doesn't work either. I received an error since the variable "name" in your suggestion is not declared... sort of. Besides, the "name" here is a column name in my Access database table. Any other ideas?

[Bob Phillips]

What, you have a column called strName?