Using LIKE in sql statement to fill recordset -- doesn't work!!

[jender624]

I'm trying to fill a recordset based on a sql statement that I generate through code. Part of the sql statement is a LIKE condition. Whenever I run the sql statement with the LIKE statement included, the recordset never opens any records (recordcount = 0, BOF and EOF are true). If I exclude the LIKE statement, the recordset populates with records.

I know the sql statement is right because it runs when I paste it into the SQL view of an Access query, which returns 5 records. Here is the sql statement:

sSql = "SELECT SWOID, "
sSql = sSql & "[tblSWO].TeamName, "
sSql = sSql & "[tblSWO].Difficulty, "
sSql = sSql & "[tblSWO].Contractor, "
sSql = sSql & "[tblSWO].[Machine#], "
sSql = sSql & "[tblSWO].Type, "
sSql = sSql & "[tblSWO].Description, "
sSql = sSql & "[tblSWO].Estimated_start_date, "
sSql = sSql & "[tblSWO].Estimated_finish_date, "
sSql = sSql & "[tblSWO].Actual_start_date, "
sSql = sSql & "[tblSWO].Actual_finish_date, "
sSql = sSql & "[tblSWO].Cost, "
sSql = sSql & "[tblSWO].Approval, "
sSql = sSql & "[tblSWO].[Completed?], "
sSql = sSql & "[tblSWO].[Estimated Man Hours], "
sSql = sSql & "[tblSWO].Priority, "
sSql = sSql & "[qrypastSWO].CountOfSWOID "
sSql = sSql & "FROM tblSWO, qrypastSWO "
sSql = sSql & "WHERE [Completed?] = 'Closed' "
sSql = sSql & "AND tblSWO.description LIKE '*" & txtdescription.Value & "*' "
sSql = sSql & "ORDER BY tblSWO.Actual_finish_date"

I'd appreciate any thoughts. Thanks!

jender624

 

[LarryDeLaruelle]

Jender:

Are you including the wild card in the Like statement?

Like somevalue & "*"



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[cmmrfrds]

What is probably happening is that you are the victum of Access defaults. If you use the query grid in Access 2000 +, then Access uses the * as the wildcard. If you are creating an ADO recordset then % is the wildcard as it is in other ANSI SQL database engines.

 

[jender624]

Yes, I am including the wildcard character. See below:

"AND tblSWO.description LIKE '*" & txtdescription.Value & "*' "

I don't understand why this would work in an Access query but not in code. I'm stumped!

jender624

 

[jender624]

THANK YOU cmmrfrds!! The "%" character worked perfectly!
I can't tell you how thankful I am; I've been working on this for way too long.

Thanks again!

jender624