VBA and ADODB.Recordset question

13 Oct 2005 5:51 PM

rmanchu

hi guys

i'm working with VBA and ADODB. i'm getting a weird error.

i've got the .Filter property set to something like this

= " Subject LIKE '*r*' OR ( To='AD' OR From LIKE '*AD*' ) "

above code runs fine, but i actually need the FIRST OR above changed to
an AND like below. now this gives me an error. is this expected? if so,
how cud i correctly do the string construction for the filter?

= " Subject LIKE '*r*' AND ( To='AD' OR From LIKE '*AD*' ) "

thanx, any help greatly appreciated
riyaz

13 Oct 2005 6:06 PM

Someone

I don't know why you have this problem, but I recommend that you use square
brackets [] around "To" and "From" fields. These are reserved keywords in
SQL. Try changing the field name to see if it has any effect.

<rman***@gmail.com> wrote in message
Show quoteHide quote

news:1129225905.191407.312050@g44g2000cwa.googlegroups.com...
>
> hi guys
>
> i'm working with VBA and ADODB. i'm getting a weird error.
>
> i've got the .Filter property set to something like this
>
> = " Subject LIKE '*r*' OR ( To='AD' OR From LIKE '*AD*' ) "
>
> above code runs fine, but i actually need the FIRST OR above changed to
> an AND like below. now this gives me an error. is this expected? if so,
> how cud i correctly do the string construction for the filter?
>
> = " Subject LIKE '*r*' AND ( To='AD' OR From LIKE '*AD*' ) "
>
> thanx, any help greatly appreciated
> riyaz
>

13 Oct 2005 6:11 PM

Jeff Johnson [MVP: VB]

"Someone" <nob***@cox.net> wrote in message
news:%233sPIDC0FHA.2212@TK2MSFTNGP15.phx.gbl...

>I don't know why you have this problem, but I recommend that you use square
>brackets [] around "To" and "From" fields. These are reserved keywords in
>SQL. Try changing the field name to see if it has any effect.

I thought so to, but the poster said that the original code worked (where
the only difference was OR vs. AND).

Riyaz, can you post the code? Or better yet, show us the actual contents of
the string variable that you're passing to the Filter property.

13 Oct 2005 6:11 PM

Tom Gaughan

Try putting 'From' in brackets. Reseverved words should be ... [From] LIKE
'%AD%'). Also use % instead of * in LIKE clauses when using ADO

<rman***@gmail.com> wrote in message
Show quoteHide quote

news:1129225905.191407.312050@g44g2000cwa.googlegroups.com...
>
> hi guys
>
> i'm working with VBA and ADODB. i'm getting a weird error.
>
> i've got the .Filter property set to something like this
>
> = " Subject LIKE '*r*' OR ( To='AD' OR From LIKE '*AD*' ) "
>
> above code runs fine, but i actually need the FIRST OR above changed to
> an AND like below. now this gives me an error. is this expected? if so,
> how cud i correctly do the string construction for the filter?
>
> = " Subject LIKE '*r*' AND ( To='AD' OR From LIKE '*AD*' ) "
>
> thanx, any help greatly appreciated
> riyaz
>

13 Oct 2005 6:44 PM

Tom Gaughan

SQL 2000 wants To to be in brackets as well...

<rman***@gmail.com> wrote in message
Show quoteHide quote

news:1129225905.191407.312050@g44g2000cwa.googlegroups.com...
>
> hi guys
>
> i'm working with VBA and ADODB. i'm getting a weird error.
>
> i've got the .Filter property set to something like this
>
> = " Subject LIKE '*r*' OR ( To='AD' OR From LIKE '*AD*' ) "
>
> above code runs fine, but i actually need the FIRST OR above changed to
> an AND like below. now this gives me an error. is this expected? if so,
> how cud i correctly do the string construction for the filter?
>
> = " Subject LIKE '*r*' AND ( To='AD' OR From LIKE '*AD*' ) "
>
> thanx, any help greatly appreciated
> riyaz
>

13 Oct 2005 7:00 PM

Douglas Marquardt

Hi riyaz:

ADODB recordset filter does not work the same as when
you get data directly from the database.
In your scenrio, I suspect that the combination of the
AND and OR conditions may be causing the problem.

Have a look in the vb docs for the Filter property of the recordset.
Here is an excerpt to get you started:

<snip>
The criteria string is made up of clauses in the form FieldName-Operator-Value (for example, "LastName = 'Smith'"). You can create
compound clauses by concatenating individual clauses with AND (for example, "LastName = 'Smith' AND FirstName = 'John'") or OR (for
example, "LastName = 'Smith' OR LastName = 'Jones'"). Use the following guidelines for criteria strings:

<snip>

hth,

Doug.

Show quoteHide quote

<rman***@gmail.com> wrote in message news:1129225905.191407.312050@g44g2000cwa.googlegroups.com...
>
> hi guys
>
> i'm working with VBA and ADODB. i'm getting a weird error.
>
> i've got the .Filter property set to something like this
>
> = " Subject LIKE '*r*' OR ( To='AD' OR From LIKE '*AD*' ) "
>
> above code runs fine, but i actually need the FIRST OR above changed to
> an AND like below. now this gives me an error. is this expected? if so,
> how cud i correctly do the string construction for the filter?
>
> = " Subject LIKE '*r*' AND ( To='AD' OR From LIKE '*AD*' ) "
>
> thanx, any help greatly appreciated
> riyaz
>

13 Oct 2005 7:20 PM

Someone

You are right. It has "limited" support for mixing AND/OR. It specifically
mention this issue in the middle of the Remarks section. See the paragraph
that starts with "There is no precedence between AND and OR":

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprofilter.asp

Show quoteHide quote

"Douglas Marquardt" <no_spam@dummy.com> wrote in message
news:esXBkhC0FHA.2884@TK2MSFTNGP09.phx.gbl...
> Hi riyaz:
>
> ADODB recordset filter does not work the same as when
> you get data directly from the database.
> In your scenrio, I suspect that the combination of the
> AND and OR conditions may be causing the problem.
>
> Have a look in the vb docs for the Filter property of the recordset.
> Here is an excerpt to get you started:
>
> <snip>
> The criteria string is made up of clauses in the form
> FieldName-Operator-Value (for example, "LastName = 'Smith'"). You can
> create
> compound clauses by concatenating individual clauses with AND (for
> example, "LastName = 'Smith' AND FirstName = 'John'") or OR (for
> example, "LastName = 'Smith' OR LastName = 'Jones'"). Use the following
> guidelines for criteria strings:
>
> <snip>
>
>
> hth,
>
> Doug.
>
>
>
> <rman***@gmail.com> wrote in message
> news:1129225905.191407.312050@g44g2000cwa.googlegroups.com...
>>
>> hi guys
>>
>> i'm working with VBA and ADODB. i'm getting a weird error.
>>
>> i've got the .Filter property set to something like this
>>
>> = " Subject LIKE '*r*' OR ( To='AD' OR From LIKE '*AD*' ) "
>>
>> above code runs fine, but i actually need the FIRST OR above changed to
>> an AND like below. now this gives me an error. is this expected? if so,
>> how cud i correctly do the string construction for the filter?
>>
>> = " Subject LIKE '*r*' AND ( To='AD' OR From LIKE '*AD*' ) "
>>
>> thanx, any help greatly appreciated
>> riyaz
>>
>
>

13 Oct 2005 7:57 PM

rmanchu

thanx guys.

i think below is the reason. hmmmmm. i reckon i'll not implement it for
now - cos rearranging that statement could get very long :(

thanx again, that was quick work :)
riyaz

Someone wrote:
Show quoteHide quote

> You are right. It has "limited" support for mixing AND/OR. It specifically
> mention this issue in the middle of the Remarks section. See the paragraph
> that starts with "There is no precedence between AND and OR":

13 Oct 2005 7:58 PM

Jeff Johnson [MVP: VB]

"Douglas Marquardt" <no_spam@dummy.com> wrote in message
news:esXBkhC0FHA.2884@TK2MSFTNGP09.phx.gbl...

> ADODB recordset filter does not work the same as when
> you get data directly from the database.
> In your scenrio, I suspect that the combination of the
> AND and OR conditions may be causing the problem.

And to expound, the filtering has nothing to do with the database but rather
acts on the in-memory representation, which is why reserved words like From
and To aren't causing problems.