SQL Query

[TimC]

New problem. I am changing a program to ADO. The following query worked before

"SELECT * FROM qryListCustomers WHERE LastName LIKE '" & txtFind.Text & "*" & "'"

to filter the records by the first letter, or first 2... But know that I have switched over to ADO, it will not. I can just put qryListCustomers after the recordset open statement, and get all the records, but if I try using this SQL statement, I get zero records.

Any ideas? I do not know much SQL, still learning

[parksie]

Don't use qryListCustomers. The SELECT statement works with tables only. If you make a composite query that does the work of both, then that'll work.

[TimC]

The SELECT statement worked with qryListCustomers before I started swapping the program over to ADO. Why won't it work now.

Composite query?

[parksie]

If qryListCustomers has already been executed, then it will have a recordset. If it hasn't, then there will be no data for it. This is why it is best to extract data direct from the table. Also, ADO works differently to DAO, so they will both have different ideas about where the data comes from. DAO was almost specifically for Jet, so it may know to execute the query first.

[JHausmann]

Have you tried changing the '*' to '%'. Wildcards changed from DAO to ADO...

[parksie]

Also, try using single rather than double quotes. Don't ask me why (because I don't know ;)), but sometimes it helps.

[TimC]

Thanks for your suggestions. The % wildcard is giving me runtime errors, and going to single quotation marks makes the program not able to find the database.

I am using this query to populate a listview. With the statement the way it is, but getting rid of the Like, I can get the full listing using either qryListCustomers, or from a table. Using the Like, I am still getting the correct columnheaders, but no data, for both a query and a table. Unfortunatly I need the like to filter the records. It seems like the Like is filtering all the records. Similar to the way my coffee filter evidently filtered all my caffeine this morning.

Any other ideas?

Thanks

[TimC]

Ah ha!

I kept fooling around and this worked:

rsListData.Open "SELECT * FROM qryListCustomers WHERE LastName LIKE '" & txtFind.Text & "%" & "'", _
dbPrimary


* for the first part and % for the second.

Thanks for all your help! I would be interested in knowing why it worked this way! I learned something, I just do not know what!

[AKA]

You learned that 'select *' means select all columns from a table and that 'LIKE "BA*"' means everything starting with BA and one more letter and that 'LIKE "BA%"' means everything starting with BA.

Just like JHausmann said, DAO and AOD changed wildcards.