Software

Ask something
Discussion Locked

Access Report Criteria

0 helpful
8 replies

by dakota99

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is it possible to get a MS Access Report to display part of it's selection criteria in the reports header or footer? For example I search a datatable for records occurring in a range of dates. Can I get the date range to appear on the reports headeror footer?
thanks in advance

8 replies

by john_wills

I do this often - it's quite important for the user looking at her reports to know what conditions she specified. Information about the criteria should be put in a VBA module; I usually structure a new type with just the items needed, then declare apublic variable of the type, thus: Type param
Assignee As String
BeginningDate As Date
EndingDate As Date
Group As String
OpenHood As Integer
Resolution As String
SortCriteriaCombination As Integer
End Type
Public Parameter As param
When the user clicks the "report" button in the form, the specifications are copied into the public variable, thus:
If IsNull(Me.BeginningDate) _
Then
Parameter.BeginningDate = 0
Else
Criterion = Criterion & " and [Open Time] >= DateValue('" & Me.BeginningDate & "')"
Parameter.BeginningDate = Me.BeginningDate
End If

The Report_Open procedure in the report's VBA reads the public parameter and constructs an appropriate header text thus:
If Parameter.BeginningDate = 0 And Parameter.EndingDate = 0 _
Then
Specification.Caption = "For All Dates"
Else
If Parameter.BeginningDate = 0 _
Then
Specification.Caption = "For Dates Through " & Parameter.EndingDate
Else
If Parameter.EndingDate = 0 _
Then
Specification.Caption = "For Dates From " & Parameter.BeginningDate
Else
Specification.Caption = "For Dates From " & Parameter.BeginningDate _ & " Through " & Parameter.EndingDate
End If
End If
End If
Please note that the "caption" property is not suggested by VBA, but it is accepted and it does work. "The specification" I have used is declared on the report as a label.

by dakota99

I apologize. It looks like you spent a lot of time preparing this answer. However, I am not knowledgeable in VBA or programming in general. Is there a function or setting in the Access design mode that will get the same result without using VBA? Also, I forgot to mention that the report is based on a query.

by john_wills

All reports are based on queries, though not all the queries are in the list of queries. I was assuming that your user entered the parameters of the report in a form. How does she enter the parameters? I do not believe that what you want can be donewithout VBA. And you might try using VBA even if you have no experience: I had no experience in it until I started using it.

by dakota99

The report (y) in my question runs query (x). The "Criteria" for the field "Date" in query (x) is:
Between[Enter a beginning date]and [Enter an ending date].

When the user runs report (y) they are prompted to enter a beginning date and then an ending date. These cannot be left blank and still have the query/report return data.

I am willing to try your VBA example. Since I have little understanding of VBA, please be as descriptive as possible in your response.
Thanks so much for your time and effort in attempting to help me through this.

by john_wills

If you send me an email request(my email address looks like my TechRepublic name) I will, subject to my manager's permission, send you a subset of a database using the parameter-passing technique.

This discussion is closed to new replies.