open query with a dynamic filter

[Nuno Guerra]

I have this query:

SELECT T_LOGS.DATE, T_LOGS.AGENT
FROM T_LOGS
WHERE [Forms]![frm_logs]![MyCriteria];

MyCriteria is populated with the command button that has:
MyCriteria = Me.Filter

When I open the query (doCmd.OpenQuery "Q_LOGS_CRITERIA"), it doesn't apply
the criteria in the 'where' statment !!

Any Help?

 

[kingston via AccessMonster.com]

Usually, query criteria is tied to a field:

SELECT T_LOGS.DATE, T_LOGS.AGENT
FROM T_LOGS
WHERE AGENT=[Forms]![frm_logs]![MyCriteria];
or
...WHERE AGENT Like "*" & [Forms]![frm_logs]![MyCriteria] & "*";

I have this query:

SELECT T_LOGS.DATE, T_LOGS.AGENT
FROM T_LOGS
WHERE [Forms]![frm_logs]![MyCriteria];

MyCriteria is populated with the command button that has:
MyCriteria = Me.Filter

When I open the query (doCmd.OpenQuery "Q_LOGS_CRITERIA"), it doesn't apply
the criteria in the 'where' statment !!

Any Help?

 

[Nuno Guerra]

Can I use the QueryDef unstead? How?

Usually, query criteria is tied to a field:

SELECT T_LOGS.DATE, T_LOGS.AGENT
FROM T_LOGS
WHERE AGENT=[Forms]![frm_logs]![MyCriteria];
or
...WHERE AGENT Like "*" & [Forms]![frm_logs]![MyCriteria] & "*";

I have this query:

SELECT T_LOGS.DATE, T_LOGS.AGENT
FROM T_LOGS
WHERE [Forms]![frm_logs]![MyCriteria];

MyCriteria is populated with the command button that has:
MyCriteria = Me.Filter

When I open the query (doCmd.OpenQuery "Q_LOGS_CRITERIA"), it doesn't apply
the criteria in the 'where' statment !!

Any Help?

 

[kingston via AccessMonster.com]

Is there a reason why you can't visually change the query design? Just open
Q_LOGS_CRITERIA in design view and add [Forms]![frm_logs]![MyCriteria] to one
of the field criteria.

Can I use the QueryDef unstead? How?

Usually, query criteria is tied to a field:

[quoted text clipped - 17 lines]

 

[Nuno Guerra]

The reason is simple. The users want to print the results from the filter
they use. When I have hide all the menu options from the main window Access,
they can not use the optin "Analyse with Excel".

So If I can export a table or a query, I want also to export the records
found when they apply a filter. The only way I see is to extract the filter
they used, apply this filter on a query and export this query to Excel.

Is there a reason why you can't visually change the query design? Just open
Q_LOGS_CRITERIA in design view and add [Forms]![frm_logs]![MyCriteria] to one
of the field criteria.

Can I use the QueryDef unstead? How?

Usually, query criteria is tied to a field:

[quoted text clipped - 17 lines]

Any Help?

 

[kingston via AccessMonster.com]

The users wouldn't do this. The developer does this once and saves the query.
The query changes dynamically based on what's filled in that control on the
form. Use an IIF statement to trap a blank and/or use a wildcard such as *.

I wasn't sure what your reference to Me.Filter meant until now. Create the
report based on the query and when the report is opened, use the form filter
in the report. Try to apply the filter in the DoCmd.OpenReport command or
use the following references:
[Reports]![ReportName].Report.Filter
[Forms]![FormName].Form.Filter

The reason is simple. The users want to print the results from the filter
they use. When I have hide all the menu options from the main window Access,
they can not use the optin "Analyse with Excel".

So If I can export a table or a query, I want also to export the records
found when they apply a filter. The only way I see is to extract the filter
they used, apply this filter on a query and export this query to Excel.

Is there a reason why you can't visually change the query design? Just open
Q_LOGS_CRITERIA in design view and add [Forms]![frm_logs]![MyCriteria] to one

[quoted text clipped - 7 lines]

 

[Nuno Guerra]

and how can I export the results to Excel instead using the Report? This is
know my principal issue, export a query with a filter used by users.

The users wouldn't do this. The developer does this once and saves the query.
The query changes dynamically based on what's filled in that control on the
form. Use an IIF statement to trap a blank and/or use a wildcard such as *.

I wasn't sure what your reference to Me.Filter meant until now. Create the
report based on the query and when the report is opened, use the form filter
in the report. Try to apply the filter in the DoCmd.OpenReport command or
use the following references:
[Reports]![ReportName].Report.Filter
[Forms]![FormName].Form.Filter

The reason is simple. The users want to print the results from the filter
they use. When I have hide all the menu options from the main window Access,
they can not use the optin "Analyse with Excel".

So If I can export a table or a query, I want also to export the records
found when they apply a filter. The only way I see is to extract the filter
they used, apply this filter on a query and export this query to Excel.

Is there a reason why you can't visually change the query design? Just open
Q_LOGS_CRITERIA in design view and add [Forms]![frm_logs]![MyCriteria] to one

[quoted text clipped - 7 lines]

Any Help?

 

[kingston via AccessMonster.com]

If the users use the built-in menu function File -> Export... on a filtered
form and select the Excel format, the resulting spreadsheet will include all
of the applied filters. You can attach this to a control on the form via:

DoCmd.RunCommand acCmdOutputToExcel

and how can I export the results to Excel instead using the Report? This is
know my principal issue, export a query with a filter used by users.

The users wouldn't do this. The developer does this once and saves the query.
The query changes dynamically based on what's filled in that control on the

[quoted text clipped - 20 lines]