Using Wildcards to Search an Access Database

[caconner]

I'm struggling to set up an ado search using the wildcard (*) character. It's an Access 2000 database, and I'm executing a command to return a recordset. The parameters to open the recordset are in a function. The user should be able enter a partial product name into a text box, hit return and a listview is populated w/ the results. I've managed to search for identical matches, but even w/ * I can't create a recordset for partials.

I've changed the commandtext every way I can think of, w/ no luck. If anyone can tell me what's wrong w/ my commandtext, it would be monumentally appreciated! Before I run screaming into the night!


Public Function OpenrsProducts()

Set rsProducts = New ADODB.Recordset
rsProducts.CursorType = adOpenStatic
rsProducts.CursorLocation = adUseClient
rsProducts.LockType = adLockPessimistic
rsProducts.Source = "Select * From Products"
rsProducts.ActiveConnection = cnApollo
rsProducts.Open
rsProducts.MoveFirst

End Function


Private Sub txtProd1_LostFocus()

Product5 = txtProd1.Text

Call OpenrsProducts
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnApollo
cmd.CommandText = "Select * from Products Where ProductName like '" & "*" & Product5 & "*" & "'"
Set rsProducts = cmd.Execute

If rsProducts.EOF = True Or rsProducts.BOF = True Then
MsgBox "There were no matches for the given search criteria.", vbOKOnly & _
vbInformation, "No Matches"
Exit Sub
Else
rsProducts.MoveFirst
For ProdIndex5 = 1 To rsProducts.RecordCount
Set lvwListItem0 = lvwProd1.ListItems.Add()
lvwListItem0.Text = ""
lvwListItem0.SubItems(1) = rsProducts!ProductName
lvwListItem0.SubItems(2) = Format(rsProducts!UnitPrice, "currency&quot
lvwListItem0.SubItems(3) = rsProducts!UnitsInStock
rsProducts.MoveNext
Next ProdIndex5
End If

End Sub

 

[EzLogic]

did you use the "Contain"??? command of a sql select?

select * from table where cField contain "HELLO"
Tekno
Wireless Toyz
Ypsilanti, Michigan

 

[dyepp19]

i dont see anything wrong with your sql query but i'm not really familiar with ado. but basically here's how would do things:

dim db as database
dim rst as recordset

set db = opendatabase("your_db_here&quot
set rst = db.openrecordset ("select * from Products where productname like '*" & Product5 & "*'&quot

if rst.recordcount > 0 then
'records found!
end if

rst.close
db.close

 

[caconner]

I tried using the contain keyword. Program errors out on the SQL.

Tried moving the *'s to exactly where dyepp19 suggested. Program doesn't error out, but still won't find matches.

Thanks for the input. Unfortunately, I'm still open to more suggestions.

Cathy

 

[dpaulson]

The wildcard character when using ADO is the % (percent).

CommandText = "Select * from Products Where ProductName like '%" & Product5 & "%'"

David Paulson

 

[caconner]

THANKS!!! I thought the wildcard for ADO/Access was something other than the *, but couldn't find any documentation on what it really was. Changed it over to a %... Works like a dream!

Thanks again!
Cathy