Reports can have at least four different data sources:

• A table.
• A query.
• A SQL statement.
• No source at all.

Tables and queries are fairly straightforward. Although a SQL statement is bit more complex, they have the advantage of accomplishing the same thing as a query without exposing the query, which might be inadvertently changed. The last option, no source at all, makes the most sense when the report is nothing but static text or the results of functions that the report might call.

I've always been a big advocate of using SQL statements rather than queries, but to maximize the flexibility of a report, a query is a better source than a SQL statement. The primary reason is that a SQL statement within a report is more difficult to manipulate than a query, which exists independently of the report. To accomplish our goal of manipulating the report, we will use a query as our source.

Conceptually, you can manipulate a query in code prior to opening the report. So, when opened, the report will use the new query that was put in place before the report was opened.

For the purposes of this exercise, we will assume that our report is called rptCaseMgr, and that we have already structured the report to show the name of a case manager in the title. To see how to do this, see theOctober issue of Access Wizard.

The particular structure used in the body of the report is not terribly important. What is critical is the data source for the report.

In this case, you want to be sure that the data source is a query in your database. Because the query that we're going to use is dynamic, I make sure that I give it a generic name. I typically use qryUtilityReport.

It makes most sense to build a query with real data, then use that query while designing the report. This is fairly straightforward. Create the query, then open a report in design view. Click on properties, then on the data tab, and choose the name of your query as a source of your report, as I've done below.

Now that we have a query in place to feed the report, the issue becomes manipulating the query. Like many objects in Microsoft Access, you can change the properties of a query not only manually, but also in code.

Below is an example of how to accomplish this.

Sub OpenCaseMgrReport(strCaseMgr As String)
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs!qryUtilityReport
strSQL = "SELECT tblDemoCaseMgr.CaseMgr, " & _ "tblDemoCaseMgr.Client " & _
"FROM tblDemoCaseMgr " & _
"WHERE (((tblDemoCaseMgr.CaseMgr)='" & strCaseMgr & "'));"
qdf.SQL = strSQL

DoCmd.OpenReport "rptCaseMgr", acViewPreview
End Sub

Let's pull this apart. Notice that our subroutine takes a variable on the way in.

Sub OpenMyReport(strCaseMgr As String)

This opening line requires an argument "strCaseMgr". We'll use this variable in our code to set the case manager for the report.

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

This section sets our variables. The querydef variable refers to a query within Access. The string variable strSQL is what we will manipulate and ultimately set as the SQL behind our query.

Set db = CurrentDb
Set qdf = db.QueryDefs!qryUtilityReport

These two lines establish the database, our current database, and the name of the query we are going to manipulate -- qryUtilityReport.

The following line establishes our SQL statement:

strSQL = "SELECT tblDemoCaseMgr.CaseMgr, " & _
"tblDemoCaseMgr.Client " & _
"FROM tblDemoCaseMgr " & _
"WHERE (((tblDemoCaseMgr.CaseMgr)='" & strCaseMgr & "'));"

This is typical concatenation, the heart of the action is in the statement:

WHERE (((tblDemoCaseMgr.CaseMgr)='" & strCaseMgr & "'));"

This is the criteria of the query, which states that the CaseMgr field must be equal to the variable passed into the routine. Note the use of single quotes before and after the double quotes surrounding strCaseMgr. This is necessary because the variable is a string variable and the single quotes are needed to indicate the text portion of the criteria.

The next line, although short and simple, is powerful:

qdf.SQL = strSQL

This statement takes the SQL statement we constructed above and moves it into our query.

After that, the code simply opens the report in a preview mode:

DoCmd.OpenReport "rptCaseMgr", acViewPreview

At this point, you will want to test the results. You can do it with code, such as the following:

Sub OpenMaryReport()
OpenCaseMgrReport "Mary Jones"
End Sub

Sub OpenAlReport()
OpenCaseMgrReport "Al Dente"
End Sub

The first routine will pass in the variable "Mary Jones" as the Case Manager, while the second will pass in "Al Dente" as the Case Manager.

It will be critical to test the routine before you put into production. The easiest way to do this is to first write code that manipulates the query and then, after the code has run successfully, open the query and inspect the results. Once you have that piece working properly, you can feed the query into your report.

We've shown you a very powerful technique this month that allows you to manipulate a query, which in turn is used as a source for a dynamic report. By using techniques such as this, we reduce the need to have similar objects in the database that need to be maintained separately.

I urge you to experiment with techniques like this to make your life simpler.