Reports, filters and queries.

[Sherwood Botsford]

I want to use access in a semi-automatic mode.

1. Filter by form on a database. NOW while this filter is applied,
run a particular report. In other words, I want the run the same
report
with any of many possible filters, without hacking about the report,
or prestoring all the filters. How do I write reports that inherit
the current filter applied in a table view?

2. I have a table with 8 boolean variables. How to I get a report to
ask, which variable to use. In essence I want a runtime where clause
in my sql query, but so far haven't figured out how to do this:

EG: Select list of stuff
FROM datatable
WHERE R1=true;

where R1 is the name of one of the boolean variables. This works.
If I replace R1 with FOO, I get a request parameter box for foo, put
in R1
and get back ALL the records.

How do I write an expression in a parameter box that will be used by
the select query?

 

[Allen Browne]

1. Add a command button to your form to open the report.
In its Click event, apply the form's filter as the WhereCondition of
OpenReport:
If Me.FilterOn Then
DoCmd.OpenReport "SomeReport", acViewPreview, , Me.Filter
End If

2. Same approach.
Place an unbound combo on your form: RowSource type as a Value List, and
RowSource of:
"R1", "R2", "...
Then open the report like this:
Dim strWhere As String
If Not IsNull (Me.MyCombo) Then
strWhere = Me.MyCombo & " = True"
DoCmd.OpenReport "SomeReport", acViewPreview, , strWhere
End If