Query to get report to show specific criteria

[Guest]

Maybe I haven't worded my search phrases properly, but I can't seem to find
an answer. I can't wrap my brain around how to get a report to return only
that data found using specific criteria. I tried following the steps in
Access Help but to no avail.

The report returns data from seventeen fields in a database. One field is
"Year" and another is "Jurisdiction". What I want to do is create a form
(pop-up or normal) that will run the query that builds the report based on
the criteria entered (or not) in the fields Year and Jurisdiction.

Some additional info. There are nine records in the table Juridiction. There
are seventeen records in the field Year (1989 through 2006) in the table
Values. The query without any criteria returns around 1,100 records. I want
to be able to enter a specific year (e. g., 2005) and a specific jurisdiction
(e. g. Cincinnati) and have the report return just those records that have
data for the year 2005 in Cincinnati.

Thank you for your assistance. Please be gentle. I'm just an old country boy
trying to make it in the big city.

 

[Allen Browne]

Create an unbound form with a combo box for selecting the Jurisdiction, a
text box for selecting the year, and a command button to open the report.

In the Click event procedure of the command button, build the WhereCondition
to use with OpenReport, based on the values entered. Something like this:

Private Sub cmdPrint_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboJurisdiction) Then
strWhere = strWhere & "(Jurisdiction = """ & Me.cboJurisdiction &
""") AND "
End If

If Not IsNull(Me.txtYear) Then
strWhere = strWhere & "([Year] = " & Me.txtYear & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Assumptions:
1. Jurisdiction is a Text field. Drop the extra quotes if it is a Number
field.
2. Year is a Number field.
3. Where the user entered nothing, you do not filter on that field.
4. You might want to filter by other fields as well, so the code is set up
to make it easy to do that.

If you need to work with dates, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html