You may find that if you have the report open in Preview when you do the
export, that it filters as desired.
This only works because the Filter of one instance interfers with the Filter
of another, so it's a pretty hairy solution. A more stable idea is to create
a query that you use explicitly for exports. Write the SQL property before
you export. This kind of thing:
Dim strWhere As String
Const strcExportQuery = "Query1"
Const strcStub = "SELECT * FROM Table1 " & vbCrLf
Const strcTail = vbCrLf & "ORDER BY SomeField;"
strWhere = "WHERE (SomeField = 999) "
CurrentDb.QueryDefs(strcExportQuery).SQL = _
strcStub & strWhere & strcTail
DoCmd.OutputTo ...
That approach works for exporting queries, and for exporting reports based
on queries, in various formats.
Another solution that works only for reports is to declare a public string
variable to hold the filter string, and apply it in Report_Open:
1. In the General Declarions section of a standard module (top, with the
Option statements), declare the variable:
Public gstrFilterString As String
2. In the code that does the export, set the variable first:
gstrFilterString = "(SomeField = 999)"
DoCmd.OutputTo ...
3. In ReportOpen, test, apply, and reset the variable:
Private Sub Report_Open(Cancel As Integer)
If gstrFilterString <> vbNullString Then
Me.Filter = gstrFilterString
Me.FilterOn = True
gstrFilterString = vbNullString
End If
End Sub
