On Thu, 20 Mar 2008 14:21:02 -0700, dsimcox
wrote:

I'm using VBA and ADO to retrieve a recordset from SQL Server - then
filtering the recordset to get the target records.

Some of my records have a numeric prefix - and some have an alpha prefix.

In my source query I use "partNo LIKE '[A-Z]%'" to extract the recordset.
But when I try to use the same logic in the recordset filter, it fails to
find the records. I've tested the result by filtering for the exact prefix
("partNo LIKE 'WW6%'") and it works, so I know the records are there. But I
need a more general way of defining the filter

Here's the filter criteria that fails. It returns NO records - when records
like WW65-ABCD exist. Can someone point me to another solution?

rsAddAllRaw.Filter = "partNo LIKE '[A-Z]%'"

I don't know why this doesn't work. It must be a quirk of Filter, although
that doesn't seem right to me. Here's another workaround if you're
interested:

sSQL = "SELECT DocID, DocTitle, left(DocVersion,1) AS Expr1 FROM
tblDocuments"

Set rs = cn.Execute(sSQL)

rs.Filter = "Expr1 '9'"

I pull the left-most character from DocVersion and get only those greater
than the string '9', which effectively gets those that start with a letter.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com