Cant change query.filter property in code Why?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to change a queries' property (filter & filteron) before
running it, instead of constructing the whole query in code. I am
constructing for example 'discipline in ('AB','CD') - assigning this to a
string and trying to set the property one of two ways: (neither have
worked). I get a message indicating that the property cannot be found.

'CurrentDb.QueryDefs(stDocName).Properties("FilterOn") = True
myquery.Properties("FilterOn") = True ' Getting error on-cant find property

the filter property is also not found:
If y = 1 Then
WhereItem = "[Discipline] In (" & """" & MyDiscipline & """"
Else
WhereItem = WhereItem & ", " & """" & MyDiscipline & """"
End If
....
WhereItem = WhereItem & ") "
....
myquery.Properties("Filter") = WhereItem

I am noticing that whle the filter property appears available via the query
builder interface, it is not listed as a querydef property. IS there a way
to do this without re-constructing the whole SQL statement?
 
Exactly how to do what you want depends on whether you're using ADOs or
DAOs, but you probably want to open a Recordset first and then filter it
and then open another Recordset based on the filtered first Recordset.
This isn't very efficient. You might be ahead just building the SQL
that you want, including the filtering information, and using those results.

According to the Help file in Access,

"You can use the Filter property to restrict the records returned from
an existing object when a new Recordset object is opened based on an
existing Recordset object. In many cases, it's faster to open a new
Recordset object by using an SQL statement that includes a WHERE clause."

My guess is, that's probably simpler to do, as well, even though it may
appear to be kind of clunky.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top