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

"dsimcox" <(E-Mail Removed)> wrote in message
news:3C99427E-AD30-43B2-86F9-(E-Mail Removed)...
> 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

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,
> 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
>
>
>

On Thu, 20 Mar 2008 14:21:02 -0700, dsimcox
<(E-Mail Removed)> 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

"dsimcox" <(E-Mail Removed)> wrote in message
news:81A3BFBD-A46E-44DA-BFE7-(E-Mail Removed)...
> 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