Running filters with queries and macros

[paulgallanter]

I’m having a problem using a query as a filter on a form.

I have a table, query and form that show all the workers and their skills
for a temp agency. I call it the “skills table, query and form.†I have
another table, query and form that show where and when people are working. I
call it the “days table, query and form.â€

On both of the forms I have a number of buttons and other controls that run
select queries as filters using the ‘applyfilter’ command in the macro
builder. Some run simple queries and some use other controls to set query
criteria. That’s not a problem. Also all of the queries, so far, are based
only on the same table that the form is based on. That’s not a problem.

Here is the problem. I want to run one filters from the skills form that
uses data from the days table and one filter from the skills form that uses
data from the day and skills form. Both of the queries that I base the
macros on run just fine But as soon as I include the query in a macro, using
the ‘applyfilter’ command, I get a dialogue box that requests an input.

I hope I gave enough information for someone to spot my error.

Thank you, paul

 

[John W. Vinson]

I’m having a problem using a query as a filter on a form.

I have a table, query and form that show all the workers and their skills
for a temp agency. I call it the “skills table, query and form.” I have
another table, query and form that show where and when people are working. I
call it the “days table, query and form.”

On both of the forms I have a number of buttons and other controls that run
select queries as filters using the ‘applyfilter’ command in the macro
builder. Some run simple queries and some use other controls to set query
criteria. That’s not a problem. Also all of the queries, so far, are based
only on the same table that the form is based on. That’s not a problem.

Here is the problem. I want to run one filters from the skills form that
uses data from the days table and one filter from the skills form that uses
data from the day and skills form. Both of the queries that I base the
macros on run just fine But as soon as I include the query in a macro, using
the ‘applyfilter’ command, I get a dialogue box that requests an input.

A Filter is NOT a Query. A Query is NOT a filter.

A valid Filter string is *part* of a query - the WHERE clause.

Or, you can use a Query as the Recordsource of a form.


John W. Vinson [MVP]

 

[paulgallanter]

Thank you for you admonishment but it does not answer my question or help me
progress. I apologize if I don’t know your language but that kind of answer
is far from welcoming and far from helpful.

All you are teaching me is to seek other sources of help. Perhaps you are
more patient with more learned people and perhaps you should refrain from
answering idiots like me.

Thank you, paul

 

[John W. Vinson]

I’m having a problem using a query as a filter on a form.

I have a table, query and form that show all the workers and their skills
for a temp agency. I call it the “skills table, query and form.” I have
another table, query and form that show where and when people are working. I
call it the “days table, query and form.”

On both of the forms I have a number of buttons and other controls that run
select queries as filters using the ‘applyfilter’ command in the macro
builder. Some run simple queries and some use other controls to set query
criteria. That’s not a problem. Also all of the queries, so far, are based
only on the same table that the form is based on. That’s not a problem.

Here is the problem. I want to run one filters from the skills form that
uses data from the days table and one filter from the skills form that uses
data from the day and skills form. Both of the queries that I base the
macros on run just fine But as soon as I include the query in a macro, using
the ‘applyfilter’ command, I get a dialogue box that requests an input.

I hope I gave enough information for someone to spot my error.

Thank you, paul

Sorry, Paul. I shouldn't have been on the newsgroups last night at all!

What I was trying to say is that you're using the wrong tool. A Query is a
standalone object (that can be used by other objects); it has a syntax like

SELECT <this field, that field, the other field>
FROM <one or more tables>
WHERE <some criteria>
ORDER BY <some field>;

with variations. The WHERE clause "filters" the query to retrieve only desired
records.

The ApplyFilter action, or the Filter property of a form, is only a small
piece of the query. It doesn't have any of the keywords SELECT, or WHERE, or
FROM, or ORDER BY; instead, it JUST has the portion of a query after the word
WHERE. For instance, a valid Filter property might be

[CustomerID] = 1387

to display just that customer's data.

Perhaps you could open your Query in SQL view and post it here. It may help
also if you post the steps of your macro, and the Recordsource of the form.
It's going to be quite possible to get your form working - but not by using a
Query in the ApplyFilter action!

Again, please accept my apologies for my inappropriate reaction yesterday.

John W. Vinson [MVP]