LIKE Syntax in SQL String

[hi there]

howdy folks,

i'm having trouble coming up with the correct syntax for a SQL statement that i'm passing to an ADO recordset. i'm trying to use a text box on a form to pass a "keyword search" value to create a ADO recordset using the LIKE function. i then loop through the ADO recordset to populate a MS ListView ActiveX control.

i think i'm getting all messed up with the quotes in quotes stuff. i've tried the following variations:

sSQL2 = "SELECT A, B, C, D, E FROM tblHAPList WHERE D LIKE" & "'*'&" & "'" & Me.txtKeyword & "'" & "&'*'"


sSQL2 = "SELECT A, B, C, D, E FROM tblHAPList WHERE D LIKE" & "'*'" & Me.txtKeyword & "'*'"

neither worked correctly however when i hard code the WHERE clause i can get it to work. the error i receive states:

"Runtime error 3021: Either BOF or EOF is true, or current record has been deleted. Requested operation requires a current record"

Any help with this problem would be greatly appreciated.

many thanks.

 

[FoFa]

Way to many quotes in there try:
WHERE D LIKE'*" & Me.txtKeyword & "*'"

 

[hi there]

hi fofa,

i tried

WHERE D LIKE'*" & Me.txtKeyword & "*'"

but still receiving the same 3021 error which occurs when trying to when navigating to the first record using the .MoveFirst() method. it doesn't appear to be returning any records because the .RecordCount property = -1, however interestingly the .BOF and .EOF properties are both set to "True".

Any ideas?

 

[DB7]

Hi there, ...is that like "Who's on First?"

ADO uses "%" as its wildcard, not "*".

 

[DB7]

...forgot to add,

too many single quotes...

sSQL2 = "SELECT A, B, C, D, E FROM tblHAPList WHERE D LIKE '%" & Me.txtKeyword & "%'"

single quotes should surround the % and the text, for a total of 2 single quotes.

" ' % " & Me.txtKeyword & " % ' "

 

[hi there]

hi DB7,

thanks so much for the help. that worked perfectly.

thanks again.