SQL Query

[TimC]

I am trying to do a SQL query to filter out a list in a list veiw. Type in a first letter of last name, to get S* list, if there are too many type a second letter Sa*, and using a refresh on the textbox _change to list the items not filtered. Seems to be working, except I believe my query is wrong, and everything is being filtered out. I know pretty much no SQL. The code I am using is:

Set rsListData = dbPrimary.OpenRecordset( _
"SELECT * FROM qryListCustomers WHERE LastName = '& txtFind.Text & "*" & "'", _
dbOpenDynaset)

How do I correct this Query?

[bbUFO]

Not sure which database are you using. But I know in Oracle is:
"SELECT * FROM qryListCoustomers WHERE LastName LIKE '" & txtfind.text & "%'"

[Psyrus]

It's the same for an Access DB except the wildcard is * not %.

[TimC]

Sorry I didn't include that. I am querying an Access 97 database, or a query of a table in Access 97. SHould I try that?

Thanks

[Psyrus]

Try replacing your = with LIKE.

[TimC]

The query worked great! There is a hiccup when I first use it. But I should be able to work that out. Thanks! This is great!

[*Vince]

Watch out for something very cruel with these wildcards if your database is Access ( not tried with SQL SERVER or ORACLE)

If you are using JET it is * the wildcard but not if you
are using an ODBC connection. The wildcard is % with ODBC...

Tricky hein ?

[TimC]

It works great. I am using an ADO DataControl. The hiccup I am having is that the first time I click on the textbox txtFind, and I type the first letter the listview refreshes itself with ALL the data, and the textbox clears. The second time I click on it and type the first letter it refreshes the listview filtered, and leaves the first letter in the textbox. I type a second letter, and it filters out more and leaves the 2 letters in the textbox. And so on. Why is it not working the first time?

I have changed the code so that the initial query for the listview uses the same code as the filtered query, just with the textbox.text="". Works, but still has the hiccup.

I set the focus to the textbox on the first load/refresh, and while the textbox kept the focus during the first refresh, it still refreshed all the data and cleared the textbox. When I typed the first letter again, it refreshed filtered and kept the first letter in the textbox. Type second letter, it still works.

I am calling for the refresh on txtFind_Change().

Any ideas?

[TimC]

Fixed it! I just called the original listview (re)fresh through the txtbox_Change. Still curious as to why that does that, but it works now. Probably not they most elegant way to do that! but hey, I'm new...