Use a Pop-Up Form To Filter Your Report

How do I use a form to specify criteria for my report using a form? There are a number of ways to do this. This post in the code archive at UtterAccess.com is really neat. It demonstrates a report popping up a form to collect criteria from the user.

Read more »

Filtering Crosstab Queries

I was cruising UtterAccess this morning and came across a thread talking about filtering crosstab queries using parameters. I remember having trouble with this once but had not really needed it lately so hadn't looked into it. The solution in the thread pointed back to two ohter UA posts with the answer:

• I have a report pulling data from a crosstab query. I want to add a date range criteria and also display the start and end date of the result.
• Need to transpose data...crosstab?

It seems it's as simple as specifying the parameter explicity in the query's parameter list. I did a little more reading and found two other good articles:

• ACC2000: Error When Running Crosstab Query with a Parameter (from Microsoft)
• Crosstab query techniques | Handle Paramaters (from Allen Browne)

The problem is summed up tersely in the Microsoft article as:

If you create a crosstab query that uses an implicit parameter [XXX] or a form reference in a WHERE clause (criteria), you may receive the following error message:

The Microsoft Jet database engine does not recognize '[XXX]' as a valid field name or expression.

And including your parameter or a reference to the form control in the query's parameter list solves it quickly and easily. Allen Browne's article has a step-by-step description of doing that.
Buried within one of the Access posts listed above is the phrase:

"You can use the Parameter names in the Report just like a Field in the RecordSource of the Report."

What's that all about? I tried it and it's as simple as that. It's redundant when you're filtering on a form control, but when you let the query prompt for the parameter you can use the parameter in your report just like a column in your recordsource. Really neat for listing the selction criteria in a report's header.
Nice- it's 7:40 on Saturday morning and I've learned two things already today!

**Some links above corrected 2011-09-05**