ADO Filter syntax

  • Thread starter Thread starter Eric Frohmann
  • Start date Start date
E

Eric Frohmann

I'm stumped here......again <sigh>

Here is the run-time result of my filter setting:
debug.Print adoRsStand.Filter
[sfmm-id]="17665503018390" and ([CruiseYear]="1996") and ([pkFlag]=true)

Fields [sfmm-id] and [CruiseYear] are both Text
Field [pkFlag] is Yes/No

The problem seems to the the double quotes are being automagically inserted.
Here's the code creating this:

stCruiseYearcriteria = " and ([CruiseYear]=" &
adoRs.Fields("CruiseYear").Value & ")"
adoRsStand.Filter = "[sfmm-id]=" & adoRs.Fields("sfmm-id").Value &
stCruiseYearcriteria & " and ([pkFlag]=true)"

I've tried several variations on the theme, the filter criteria always comes
up with zilch although when I apply the filter interactively I get the
correct results...
Here's my test:

bNoRecs = Eval(adoRsStand.EOF = True)


FTR, here's my original statments which didn't work either:

' stCruiseYearcriteria = " and (CruiseYear='" &
adoRs.Fields("CruiseYear") & "')"
' adoRsStand.Filter = "[sfmm-id]='" & adoRs.Fields("sfmm-id") &
"'" & stCruiseYearcriteria
The rn-time result is:
debug.Print adoRsStand.Filter
[sfmm-id]='"17665503018390"' and ([CruiseYear]='"1996"') and ([pkFlag]=true)

WhaddaIgottaDo????
TIA
 
OK - option 2.

Is there a better _quick_ way of checking if the record already exists based
on multiple criteria?
 
Alright - here's what's going on - hopefully someone can tell me why...

If I set the filter in code as:
adoRsStand.Filter = "[sfmm-id]=17665503018390 and [CruiseYear]=1996
and [pkFlag]=true"
I receive the required result.

How can I create this in code without the quotes???? My current effort is:
adoRsStand.Filter = "[sfmm-id]=" & adoRs.Fields("sfmm-id").Value &
stCruiseYearcriteria & " and [pkFlag]=true"
which results in the filter value being:
adoRsStand.Filter = "[sfmm-id]="17665503018390" and
[CruiseYear]="1996" and [pkFlag]=true"

TIA
 
Eric Frohmann said:
Alright - here's what's going on - hopefully someone can tell me why...

If I set the filter in code as:
adoRsStand.Filter = "[sfmm-id]=17665503018390 and [CruiseYear]=1996
and [pkFlag]=true"
I receive the required result.

How can I create this in code without the quotes???? My current effort is:
adoRsStand.Filter = "[sfmm-id]=" & adoRs.Fields("sfmm-id").Value &
stCruiseYearcriteria & " and [pkFlag]=true"
which results in the filter value being:
adoRsStand.Filter = "[sfmm-id]="17665503018390" and
[CruiseYear]="1996" and [pkFlag]=true"

TIA
Click to expand...


What are the types associated with sfmm-id and CruiseYear? If they are
strings then you must use single quotes:

adoRsStand.Filter = "[sfmm-id]= ' " & adoRs.Fields("sfmm-id").Value & "'"
 
Back
Top