recordset problem when using *text* operator

[yoyo138]

hello,

i am trying to populate a listbox with an sqlstring while at the same time using a recordset (using same sqlstring) to tally-up values from a particular field.

as of right now, the listbox populates w/o error, but when i [try to] initialize my recordset, nothing happens (except eof=true)
it seems that anytime i use string/text for my search condition, recordcount is 0 (even though my listbox displays proper output). if i use numeric data for search, the recordset initializes properly and i get no errors.

if anyone has any suggestions with this that would be great.

here is a sample of my code:
Dim rs As New ADODB.Recordset
dim strSQL as string

strSQL = "SELECT * FROM Clients WHERE Notes Like " & Chr(34) & "something" & Chr(34)
lstBox.RowSource = strSQL
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Not rs.EOF Then
msgbox rs.RecordCount
End If

rs.Close

Set rs = Nothing

Thanks,
DC

 

[Andrzejek]

Hmmm, if that happens your RecordCount should show -1

Try:

If Not rs.EOF Then
    [red]rs.MoveLast[/red]
    msgbox rs.RecordCount
End If

HTH

---- Andy

 

[PHV]

Try this:

strSQL = "SELECT * FROM Clients WHERE Notes Like '" & "something" & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pbrodsky]

I think you are missing the wildcard character after your search term.

 

[PHV]

As you use ADO I suggest single quotes for text literal and % as wildcar.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Andrzejek]

This "something" may be:

something = "'James%'"

---- Andy

 

[Swi]

This may be of some help:

thread222-889231

Swi