Undocumented Filter?

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

Guest

Crosstab queries (perhaps among other sorts of queries), do not have a Filter
property.

But create a form with a subform control, and put in the SourceObject
Property of the sf-control "Query.MyCTQuery" and you can do a filter-by-form
or a filter-on-selection.

But, when you have done that, test the various places you might find the
query's filter:

Me.Filter
....will be empty

Me.SubformControl.Form.Filter
....will give you an error

set qd = currentdb.querydefs("MyCTQuery")
qd.Properties("Filter")
....will give you an error that the property does not exist


Is there someplace else where this Filter is being stored, someplace that we
can get at it?
 
queries don't have filters. That is one of the things you use queries for is
to do filtering. For example if you create a query and in the criteria for
[City] you enter <> "New York", then the resulting recordset will include all
rows except where the City field is = "New York". Guess what? That is a
filter!
 
Build a Select query. Right click in the table space and choose to view
properties for the query.

Filter is there.

Of course, you can't reference the Filter unless it has already been set
once, but after that, it is saved alongside the query.

But that is not my question. CrossTab queries do not have that Filter
property, yet you can still do a Filter-By-Form on them. I want to know how
to get at that filter so that when I print or export to excel, I am
printing/exporting exactly what the user is looking at (including a filtered
recordset).
 
Filters apply only to Form objects and Report objects. If you create a Filter
in a query definition or a table definition and then create a form or report
with the query or table as the record source, the form or report inherits the
filter. It has no effect on what the query returns.

Only Select queries can be record sources. If you look at any other query
type, you will not see the filter property.

Klatuu said:
queries don't have filters. That is one of the things you use queries for is
to do filtering. For example if you create a query and in the criteria for
[City] you enter <> "New York", then the resulting recordset will include all
rows except where the City field is = "New York". Guess what? That is a
filter!

Tim said:
Crosstab queries (perhaps among other sorts of queries), do not have a Filter
property.

But create a form with a subform control, and put in the SourceObject
Property of the sf-control "Query.MyCTQuery" and you can do a filter-by-form
or a filter-on-selection.

But, when you have done that, test the various places you might find the
query's filter:

Me.Filter
...will be empty

Me.SubformControl.Form.Filter
...will give you an error

set qd = currentdb.querydefs("MyCTQuery")
qd.Properties("Filter")
...will give you an error that the property does not exist


Is there someplace else where this Filter is being stored, someplace that we
can get at it?
Click to expand...
Click to expand...
 
I am not talking about RecordSource, I am talking about SourceObject. You can
display a crosstab query in a subform control by setting the SourceObject of
the control to "Query.QueryName".

I know your next question is "why would you do that when a form is so much
more functional?" The answer to that question is because these are queries
that the user of this database might build to look at data in a particular
way. I have an interface built that will let that user "enable" the query for
viewing as the sourceobject of the sub-form control. I want the user to only
have to worry about creating the query, not another form.

But all that being said and setting debates about the use of Filters aside,
can you say where one could find the Filter that is being applied to a
subform that is displaying a Cross-Tab query? Like my first post said, you
won't find it in the main form's Filter property, nor the subform.form.filter
property. Yet, you can filter those records.

Klatuu said:
Filters apply only to Form objects and Report objects. If you create a Filter
in a query definition or a table definition and then create a form or report
with the query or table as the record source, the form or report inherits the
filter. It has no effect on what the query returns.

Only Select queries can be record sources. If you look at any other query
type, you will not see the filter property.

Klatuu said:
queries don't have filters. That is one of the things you use queries for is
to do filtering. For example if you create a query and in the criteria for
[City] you enter <> "New York", then the resulting recordset will include all
rows except where the City field is = "New York". Guess what? That is a
filter!

Tim said:
Crosstab queries (perhaps among other sorts of queries), do not have a Filter
property.

But create a form with a subform control, and put in the SourceObject
Property of the sf-control "Query.MyCTQuery" and you can do a filter-by-form
or a filter-on-selection.

But, when you have done that, test the various places you might find the
query's filter:

Me.Filter
...will be empty

Me.SubformControl.Form.Filter
...will give you an error

set qd = currentdb.querydefs("MyCTQuery")
qd.Properties("Filter")
...will give you an error that the property does not exist


Is there someplace else where this Filter is being stored, someplace that we
can get at it?
Click to expand...
Click to expand...
Click to expand...
 
No, Tim, I would not ask why you want to do that. To me, that is the most
stupid question there is. What you are about is a very reasonable way to do
it.

You say you are able to filter the records in the sub form now. My question
is how are you filtering those records. Normally, the filter property for a
sub form would be indentified in the sub form. Since you are using a crosstab
query as a source object, that will not work. The filtering would be done in
the Criteria row of the query.

Now, what I don't know for sure is whether the crosstab used a sourceobject
would refresh if you say like use a combo box to select the filter values and
put the value of the combo box in the criteria row of the cross tab and did a
requery in the After Update event of the combo box. Might be worth
experimenting with.

Tim said:
I am not talking about RecordSource, I am talking about SourceObject. You can
display a crosstab query in a subform control by setting the SourceObject of
the control to "Query.QueryName".

I know your next question is "why would you do that when a form is so much
more functional?" The answer to that question is because these are queries
that the user of this database might build to look at data in a particular
way. I have an interface built that will let that user "enable" the query for
viewing as the sourceobject of the sub-form control. I want the user to only
have to worry about creating the query, not another form.

But all that being said and setting debates about the use of Filters aside,
can you say where one could find the Filter that is being applied to a
subform that is displaying a Cross-Tab query? Like my first post said, you
won't find it in the main form's Filter property, nor the subform.form.filter
property. Yet, you can filter those records.

Klatuu said:
Filters apply only to Form objects and Report objects. If you create a Filter
in a query definition or a table definition and then create a form or report
with the query or table as the record source, the form or report inherits the
filter. It has no effect on what the query returns.

Only Select queries can be record sources. If you look at any other query
type, you will not see the filter property.

Klatuu said:
queries don't have filters. That is one of the things you use queries for is
to do filtering. For example if you create a query and in the criteria for
[City] you enter <> "New York", then the resulting recordset will include all
rows except where the City field is = "New York". Guess what? That is a
filter!

:

Crosstab queries (perhaps among other sorts of queries), do not have a Filter
property.

But create a form with a subform control, and put in the SourceObject
Property of the sf-control "Query.MyCTQuery" and you can do a filter-by-form
or a filter-on-selection.

But, when you have done that, test the various places you might find the
query's filter:

Me.Filter
...will be empty

Me.SubformControl.Form.Filter
...will give you an error

set qd = currentdb.querydefs("MyCTQuery")
qd.Properties("Filter")
...will give you an error that the property does not exist


Is there someplace else where this Filter is being stored, someplace that we
can get at it?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your continued help with this, Klatuu.

BTW, any reference to a Bruce Campbell movie in your nickname, "Klatuu"?

Here is the thing...

The user makes a query and enables it (through an entry an a utility table)
to be a possible selection for this subform. The query will show up in a
combobox so that the user can select the query, and the sourceobject of the
subform is then set to that query ("Query.MyCTQuery").

Now that the user is viewing the crosstab query, they use normal
Filter-By-Form Functionality to filter their recordset. They aren't in the
query design view to have access to the criteria row, as you described, and
if I set a querydef object equal to the query (even after a save), nothing
shows in the SQL as having been changed (as you would expect if a
modification had been made to the criteria line of the query). The query is
just being filtered somehow. Any other idea where or how?

Klatuu said:
No, Tim, I would not ask why you want to do that. To me, that is the most
stupid question there is. What you are about is a very reasonable way to do
it.

You say you are able to filter the records in the sub form now. My question
is how are you filtering those records. Normally, the filter property for a
sub form would be indentified in the sub form. Since you are using a crosstab
query as a source object, that will not work. The filtering would be done in
the Criteria row of the query.

Now, what I don't know for sure is whether the crosstab used a sourceobject
would refresh if you say like use a combo box to select the filter values and
put the value of the combo box in the criteria row of the cross tab and did a
requery in the After Update event of the combo box. Might be worth
experimenting with.

Tim said:
I am not talking about RecordSource, I am talking about SourceObject. You can
display a crosstab query in a subform control by setting the SourceObject of
the control to "Query.QueryName".

I know your next question is "why would you do that when a form is so much
more functional?" The answer to that question is because these are queries
that the user of this database might build to look at data in a particular
way. I have an interface built that will let that user "enable" the query for
viewing as the sourceobject of the sub-form control. I want the user to only
have to worry about creating the query, not another form.

But all that being said and setting debates about the use of Filters aside,
can you say where one could find the Filter that is being applied to a
subform that is displaying a Cross-Tab query? Like my first post said, you
won't find it in the main form's Filter property, nor the subform.form.filter
property. Yet, you can filter those records.

Klatuu said:
Filters apply only to Form objects and Report objects. If you create a Filter
in a query definition or a table definition and then create a form or report
with the query or table as the record source, the form or report inherits the
filter. It has no effect on what the query returns.

Only Select queries can be record sources. If you look at any other query
type, you will not see the filter property.

:

queries don't have filters. That is one of the things you use queries for is
to do filtering. For example if you create a query and in the criteria for
[City] you enter <> "New York", then the resulting recordset will include all
rows except where the City field is = "New York". Guess what? That is a
filter!

:

Crosstab queries (perhaps among other sorts of queries), do not have a Filter
property.

But create a form with a subform control, and put in the SourceObject
Property of the sf-control "Query.MyCTQuery" and you can do a filter-by-form
or a filter-on-selection.

But, when you have done that, test the various places you might find the
query's filter:

Me.Filter
...will be empty

Me.SubformControl.Form.Filter
...will give you an error

set qd = currentdb.querydefs("MyCTQuery")
qd.Properties("Filter")
...will give you an error that the property does not exist


Is there someplace else where this Filter is being stored, someplace that we
can get at it?
Click to expand...
Click to expand...
Click to expand...
 
BTW, any reference to a Bruce Campbell movie in your nickname, "Klatuu"?

The phrase that Campbell had so much trouble with (Gort, Klatuu barada
nikto) is a quote from the movie, The Day the Earth Stood Still. It was an
phrase given to Patricia Neal to pass on to the robot, Gort, telling him
that Klatuu didn't want him to level Washington D.C. (and possibly the rest
of the world!)

Tom Lake
 
No, from a 1954 (about) movie "The Day the Earth Stood Still"
My favorite Bruce Campell was "Brisco County Jr."
Hey, if you come up with a solution, I would appreciate it if you would post
back. This is an interesting problem. I have not used a crosstab as a
sourceobject before. In fact, it never occurred to me, but I can see some
value and it give me some ideas.

So, my concept is:
create a combo box that would have a row source of the values you want to
include along with the famous *. There are several sites that show how to
include this in a row source if you are using a query. Then in the criteria
row of the crosstab for the field column you want to filter on:
= Like forms!MyFormName.Mycbo

Then make * the default value of the combo box and in the after update of
the combo do a requery. The question here, is what to requery? The form, the
control, or what? I wonder if it would take a Refresh instead.

Good luck with it. I look forward to hearing from you.

Klattuu, varada nicto

Tim said:
Thanks for your continued help with this, Klatuu.

BTW, any reference to a Bruce Campbell movie in your nickname, "Klatuu"?

Here is the thing...

The user makes a query and enables it (through an entry an a utility table)
to be a possible selection for this subform. The query will show up in a
combobox so that the user can select the query, and the sourceobject of the
subform is then set to that query ("Query.MyCTQuery").

Now that the user is viewing the crosstab query, they use normal
Filter-By-Form Functionality to filter their recordset. They aren't in the
query design view to have access to the criteria row, as you described, and
if I set a querydef object equal to the query (even after a save), nothing
shows in the SQL as having been changed (as you would expect if a
modification had been made to the criteria line of the query). The query is
just being filtered somehow. Any other idea where or how?

Klatuu said:
No, Tim, I would not ask why you want to do that. To me, that is the most
stupid question there is. What you are about is a very reasonable way to do
it.

You say you are able to filter the records in the sub form now. My question
is how are you filtering those records. Normally, the filter property for a
sub form would be indentified in the sub form. Since you are using a crosstab
query as a source object, that will not work. The filtering would be done in
the Criteria row of the query.

Now, what I don't know for sure is whether the crosstab used a sourceobject
would refresh if you say like use a combo box to select the filter values and
put the value of the combo box in the criteria row of the cross tab and did a
requery in the After Update event of the combo box. Might be worth
experimenting with.

Tim said:
I am not talking about RecordSource, I am talking about SourceObject. You can
display a crosstab query in a subform control by setting the SourceObject of
the control to "Query.QueryName".

I know your next question is "why would you do that when a form is so much
more functional?" The answer to that question is because these are queries
that the user of this database might build to look at data in a particular
way. I have an interface built that will let that user "enable" the query for
viewing as the sourceobject of the sub-form control. I want the user to only
have to worry about creating the query, not another form.

But all that being said and setting debates about the use of Filters aside,
can you say where one could find the Filter that is being applied to a
subform that is displaying a Cross-Tab query? Like my first post said, you
won't find it in the main form's Filter property, nor the subform.form.filter
property. Yet, you can filter those records.

:

Filters apply only to Form objects and Report objects. If you create a Filter
in a query definition or a table definition and then create a form or report
with the query or table as the record source, the form or report inherits the
filter. It has no effect on what the query returns.

Only Select queries can be record sources. If you look at any other query
type, you will not see the filter property.

:

queries don't have filters. That is one of the things you use queries for is
to do filtering. For example if you create a query and in the criteria for
[City] you enter <> "New York", then the resulting recordset will include all
rows except where the City field is = "New York". Guess what? That is a
filter!

:

Crosstab queries (perhaps among other sorts of queries), do not have a Filter
property.

But create a form with a subform control, and put in the SourceObject
Property of the sf-control "Query.MyCTQuery" and you can do a filter-by-form
or a filter-on-selection.

But, when you have done that, test the various places you might find the
query's filter:

Me.Filter
...will be empty

Me.SubformControl.Form.Filter
...will give you an error

set qd = currentdb.querydefs("MyCTQuery")
qd.Properties("Filter")
...will give you an error that the property does not exist


Is there someplace else where this Filter is being stored, someplace that we
can get at it?
Click to expand...
Click to expand...
Click to expand...
 
That was not Bruce Campbell, it was Micheal Rennie. The movie was made in
1951. Bruce Campbell was born in 1958 (June 22 to be exact)
There is debate on what the phrase actually meant. Some will agree with
you, others say it means "come get me"; however, I think he said "Never use
GoTo"
 
Klatuu,

I think Tom was referring to the same movie I was, "Army of Darkness,"
starring Bruce Campbell. What I am coming to realize is that AoD must have
paid homage to tDtESS in having Bruce's character have to remember that same
line from tDtESS. So, the line is in both movies, you are just referencing
the original movie, while I did not know that was the source for the AoD line.

BTW, I appreciate your proposed solution, but I have to take into account
the fact that the user might be filtering on multiple fields for different
values. In order to account for them all, each of the fields of each query
would have to be represented in a combobox, and that would just get messy.

I currently re-mask the SQL of the source query by taking a query I've set
aside for this task and rewriting the SQL property...

set qd = currentdb.querydefs("DynamicQuery")
qd.SQL = "SELECT * FROM [" & me.cboEnabledQueries & "]"

That solution gives me a Filter property for the query (since I'm working
with a SELECT query again), but that solution necessitates re-writing the
Filter property:

qd.Filter = qd.fields(0) & " Like *"

....and the OrderBy property, etc.

That gets to be a problem when I have a parameter in the query, too. All in
all, when I change from viewing one query to viewing another that has
parameters, I end up getting prompted 4 or 5 times for the same parameter. It
works, but it is very messy.

If I could just get beyond the crosstab query not having a filter property,
and get at the filter wherever it is being used, I could go away from the
DynamicQuery remasking the SQL, and just show the crosstab query itself.

Anyway, if you have more input, I will continue to follow this thread.
Otherwise, I will post back when I find an adequate solution. I have been
fighting this for so long... I feel like I'm trying to get a 4-bolt rim for a
car tire onto a 5-bolt axle. Things just don't line up in all places at once.

Thanks again.
 
I have heard of AoD, but never seen it. I get asked about it a lot. Guess I
should go find a copy and watch it. TdteSS is probably my all time favorite.
I was 9 yrs old at the time, and it kind of set me on a SciFi path. The
reason for the misspelling is that I was on a BB where someone else had used
the correct spelling, so I changed it so I could use it, so it has been that
way a long time.

You gotta get over the filter thing. I suggest you go into help and read up
on the filter property. Maybe it will make things clear.
I know the multiple combos would be messy, but that is why I suggested the
approach of adding * to the combo. That way, if no selection were made, all
rows would return. I still suspect refreshing the crosstab might be a
problem while it is being used as a sourceobject. I'm so curious about this
I want to give it a try. The problem is, I have to actually do some
productive work here :)

Good Luck

Tim said:
Klatuu,

I think Tom was referring to the same movie I was, "Army of Darkness,"
starring Bruce Campbell. What I am coming to realize is that AoD must have
paid homage to tDtESS in having Bruce's character have to remember that same
line from tDtESS. So, the line is in both movies, you are just referencing
the original movie, while I did not know that was the source for the AoD line.

BTW, I appreciate your proposed solution, but I have to take into account
the fact that the user might be filtering on multiple fields for different
values. In order to account for them all, each of the fields of each query
would have to be represented in a combobox, and that would just get messy.

I currently re-mask the SQL of the source query by taking a query I've set
aside for this task and rewriting the SQL property...

set qd = currentdb.querydefs("DynamicQuery")
qd.SQL = "SELECT * FROM [" & me.cboEnabledQueries & "]"

That solution gives me a Filter property for the query (since I'm working
with a SELECT query again), but that solution necessitates re-writing the
Filter property:

qd.Filter = qd.fields(0) & " Like *"

...and the OrderBy property, etc.

That gets to be a problem when I have a parameter in the query, too. All in
all, when I change from viewing one query to viewing another that has
parameters, I end up getting prompted 4 or 5 times for the same parameter. It
works, but it is very messy.

If I could just get beyond the crosstab query not having a filter property,
and get at the filter wherever it is being used, I could go away from the
DynamicQuery remasking the SQL, and just show the crosstab query itself.

Anyway, if you have more input, I will continue to follow this thread.
Otherwise, I will post back when I find an adequate solution. I have been
fighting this for so long... I feel like I'm trying to get a 4-bolt rim for a
car tire onto a 5-bolt axle. Things just don't line up in all places at once.

Thanks again.

Klatuu said:
That was not Bruce Campbell, it was Micheal Rennie. The movie was made in
1951. Bruce Campbell was born in 1958 (June 22 to be exact)
There is debate on what the phrase actually meant. Some will agree with
you, others say it means "come get me"; however, I think he said "Never use
GoTo"
Click to expand...
Click to expand...
 
Tom Lake said:
The phrase that Campbell had so much trouble with (Gort, Klatuu barada
nikto) is a quote from the movie, The Day the Earth Stood Still. It
was an phrase given to Patricia Neal to pass on to the robot, Gort,
telling him that Klatuu didn't want him to level Washington D.C.
(and possibly the rest of the world!)
Click to expand...

Except that I'm pretty sure that the name of Michael Rennie's character
was spelled "Klaatu", not "Klatuu".
 
The spelling was klaatu. I purposely misspelled it because I wanted to use
the name for a BB screen name several years ago and someone else had used the
correct spelling. I guess I got into the habit of spelling it incorrectly.

Back on topic, I did some experimenting with this over the weekend and found
that you cannot set a filter for a crosstab query or a query a crosstab is
based on.
 
Back
Top