ADO Recordset Filter for alpha

[dsimcox]

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]%'"

 

[GysdeJongh]

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]%'"

Hi dsimcox,
just maybe if you retrieve the set from the SQL server T-SQL is used in a
stored procedure (?) Then the % is a wildcard character.If you use VBA and
ADO then * is the wild card character.

Try rsAddAllRaw.Filter = "partNo LIKE '[A-Z]*'"
Just for fun
hth
Gys

 

[dsimcox]

Thanks for the reply Gys . . .

Your suggestion did not work for me, I'm sorry to say. I have other filters
on this recordset that also use the % wildcard that are functioning properly
- so I think this is the correct wildcard to use in my VBA code.

I was able to work around this problem by creating a specific query for this
case - avoiding the need to filter the recordset - but I'd sure like to know
how to filter for a string that begins with alpha characters.

 

[Dick Kusleika]

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.

 

[GysdeJongh]

Thanks for the reply Gys . . .

Your suggestion did not work for me, I'm sorry to say. I have other
filters
on this recordset that also use the % wildcard that are functioning
properly
- so I think this is the correct wildcard to use in my VBA code.

I was able to work around this problem by creating a specific query for
this
case - avoiding the need to filter the recordset - but I'd sure like to
know
how to filter for a string that begins with alpha characters.

Hi dsimcox,
what does your workaround looklike ?

For me the filter on the record set only works with a client side- and
static cursor.Have you tried that combination of parameters ?
Gys