using wildcard in an ADO recordset to SQL 2000

[mgardiner]

I am trying to run a Find on a ADO recordset using
an SQL syntax for the Find.
I need to run for a WildCard Character.
here is my code.
----------------------------------------------------------------

Dim adoQuoteRS As Recordset
'create a recordset to search on
Dim strsql As String
Set adoQuoteRS = New Recordset
strsql = "SELECT tblQuotes.*, tblQuoteLog.*, tblProjects.* " & _
"FROM tblProjects " & _
"INNER JOIN tblQuotes ON tblProjects.ProjectsID = tblQuotes.ProjectID " & _
"INNER JOIN tblQuoteLog ON tblQuotes.QuoteID = tblQuoteLog.QuoteID"

adoQuoteRS.Open strsql, g_objConn, adOpenStatic

If FindLookIn = "QuotationNo" Then
'If FindLookIn = "Entire Database" Then
With adoQuoteRS
.MoveFirst
'.Find (FindLookIn & "= '" & findvalue & "'&quot
.Find ("QuotationNo = '%10881'&quot
'WHERE (QuotationNo LIKE '%10881')
If .BOF Then
MsgBox "Matching Record not Found"
End If
End With
End If

adoQuoteRS.Close
---------------------------------------------------------------

I cannot get VB to accept the LIKE as valid If I just look for the
entire value then I can find it with out using a wildcard character.
any help would be appreciated.

Also when I open my recordset in dynamic mode I get a recordcount of -1 ????

 

[HebieBug]

Wildcard character should be ='thevalue%'
-1 comes up when using the in correct connection type or some times non at all
Why not give this one a try
RECORDSET.CursorLocation = adUseClient
CONNECTION.Open strSql, aMainrecConn, adOpenStatic, adLockPessimistic

 

[woyler]

As far as the record count you have to open your recordset as static or keyset.

Try adding the second'%' and lose the ()
With adoQuoteRS
.MoveFirst
.Find "QuotationNo = '%10881%"
If .BOF Then
MsgBox "Matching Record not Found"
End If
End With

 

[mgardiner]

I am trying to find the value "010881"
I tried the % wildcard character in back and in front of my string with no luck.
I know the value is in the database but the search goes completely through
without finding it like it was looking for "%10881" not anything"10881".
where % is an actual character and not a wildcharacter.
I did switch to the adOpenstatic and the recordset returns the correct count.
thank you for your help. If you have any other suggestions please let me know.
Morgan

 

[mgardiner]

Thanks
Everyone I appreciate the help.
RJ5 in the SQL Forum instructed me to use the LIKE keyword.

I tried the % in different places and came up with mixed results.
I don't know why yet but I think I can work around it.

.find ("QuotationNO LIKE '%10881%'&quot
Works fine

.find ("QuotationNO LIKE '10881%'&quot
Works fine to find the number and anything behind the text

.find ("QuotationNO LIKE '%10881'&quot
gives me a Error 3001 arguments are of the wrong type.

I don't know why this would be.
but %10881% will have to work for me for right now until someone can
explain it to me.
Thanks Morgan

 

[woyler]

sorry morgan , I meant to post my response using 'LIKE', I just copied the wrong part of your code. Keep in mind that your results will vary even more if you use a numeric data type