Filter a query/report with OutputTo

[The Dude]

Hello,

I have a report that is displayed and filtered with a combobox.
When the user presses the button, it shows the report filtered by the
content of the combobox with the OpenReport command.
Problem is: how can I do the same button so that the filtered data is
transferred directly to Excel?

I have tried the OutputTo command but it does not seem to be able to filter
the report or query; consequently it exports the whole query data.

Thank you for your invaluable help
T_D

 

[Allen Browne]

One way is to declare a public string variable, and assign it the filter
string you need before you output the report. In the report's Open event
procedure, you examine the string, assign it to the report's Filter
(remmeber to set FilterOn as well), and clear the variable.

Where possible, I prefer to export a query rather than a report. You create
a query for the purpose, and then assign the right SQL statement to it so
the right info is exported.

Post back if you need further details of either approach.

 

[The Dude]

Hello Allen

Thank you for the great tip!
I like the Open_Report method and will try to use it. Unfortunately I have
tried the public variable but I can't make it work.

Basically I assign to the public variable the value of the combobox on the
launching form; then on the open_report event I set the me.filter = [field] =
MyPublicVar
The problem is that the program does not recognize the variable although
it's declared public. I've checked the spelling and it's correct.
I've also tried to declare the variable as static inside the procedure but
it does not work either.

Do you have a clue on what I have missed?

Thanks
T_D

 

[Allen Browne]

Steps:

1. Create a new module.
Open the code window (Ctrl+G)
Choose Module on the Insert menu.

2. In the General Declarations (just below the Option statements):
Public gstrReportFilter As String

3. Save the module with a name such as Module1.

4. In the Open event procedure of your report, apply and clear the filter:

Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

5. In the code that calls the report, set the filter string first, e.g.:
gstrReportFilter = "[SomeField] = 999"
DoCmd.OutputTo acOutputReport, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen

Thank you for the great tip!
I like the Open_Report method and will try to use it. Unfortunately I have
tried the public variable but I can't make it work.

Basically I assign to the public variable the value of the combobox on the
launching form; then on the open_report event I set the me.filter =
[field] =
MyPublicVar
The problem is that the program does not recognize the variable although
it's declared public. I've checked the spelling and it's correct.
I've also tried to declare the variable as static inside the procedure but
it does not work either.

Do you have a clue on what I have missed?

Thanks
T_D

One way is to declare a public string variable, and assign it the filter
string you need before you output the report. In the report's Open event
procedure, you examine the string, assign it to the report's Filter
(remmeber to set FilterOn as well), and clear the variable.

Where possible, I prefer to export a query rather than a report. You
create
a query for the purpose, and then assign the right SQL statement to it so
the right info is exported.

Post back if you need further details of either approach.

 

[The Dude]

You're a king Allen!

Thanks a lot for solving my problem, and granting me with this great tip
which will most probably find a hundred other uses...

Steps:

1. Create a new module.
Open the code window (Ctrl+G)
Choose Module on the Insert menu.

2. In the General Declarations (just below the Option statements):
Public gstrReportFilter As String

3. Save the module with a name such as Module1.

4. In the Open event procedure of your report, apply and clear the filter:

Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

5. In the code that calls the report, set the filter string first, e.g.:
gstrReportFilter = "[SomeField] = 999"
DoCmd.OutputTo acOutputReport, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen

Thank you for the great tip!
I like the Open_Report method and will try to use it. Unfortunately I have
tried the public variable but I can't make it work.

Basically I assign to the public variable the value of the combobox on the
launching form; then on the open_report event I set the me.filter =
[field] =
MyPublicVar
The problem is that the program does not recognize the variable although
it's declared public. I've checked the spelling and it's correct.
I've also tried to declare the variable as static inside the procedure but
it does not work either.

Do you have a clue on what I have missed?

Thanks
T_D

One way is to declare a public string variable, and assign it the filter
string you need before you output the report. In the report's Open event
procedure, you examine the string, assign it to the report's Filter
(remmeber to set FilterOn as well), and clear the variable.

Where possible, I prefer to export a query rather than a report. You
create
a query for the purpose, and then assign the right SQL statement to it so
the right info is exported.

Post back if you need further details of either approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"The Dude" <f_com2°AT°yahoo°DOT°fr> wrote in message

I have a report that is displayed and filtered with a combobox.
When the user presses the button, it shows the report filtered by the
content of the combobox with the OpenReport command.
Problem is: how can I do the same button so that the filtered data is
transferred directly to Excel?

I have tried the OutputTo command but it does not seem to be able to
filter
the report or query; consequently it exports the whole query data.

Thank you for your invaluable help
T_D

 

[The Dude]

Hello again,

How would you do the exact same thing with a query?

It works great on forms, but I cannot seem to find an open event on the
queries, and neither can I find a criteria statement in the docmd.openquery
command...
So if I want to export a query directly to excel, how can I set a filter
criteria OR redefine the SQL statement?

Thanks in advance.
TD

 

[Allen Browne]

Queries don't have that kind of power. Use a form instead.

The form can be in Datasheet view if you want it to look like a query.