Software

Ask something
Discussion Locked

Access 2000 Query Criteria on Report

0 helpful
7 replies

by benwagg

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

How do I show the criteria that a query has been run against. The criteria are not pop-up parameters for the report like start-date & end-date, but criteria in the underlying query.

Inexact Example: If we need separate reports of widget sales forfield-salesmen and retail-counter between [start-date] and [end-date], I may create different reports titled TOTAL SALES, but I would like to show in the header the exact criteria used in the underlying queries of the reports, so that my report headers might look like:

TOTAL SALES between [1/1/2002] and [12/31/2002]
Region: Southern States
AccountRep: Is Not Null

TOTAL SALES between [1/1/2002] and [12/31/2002]
Region: Southern States
AccountRep: Is Null

TOTAL SALES between [1/1/2002] and [12/31/2002]
Region: Northern States
AccountRep: Is Not Null

TOTAL SALES between [1/1/2002] and [12/31/2002]
Region: Northern States
AccountRep: Is Null

My inexact example is, of course, inexact. I understand how pop-up params work, and the queries are much more complex than they would be practical for. For audit or verification purposes, it is useful to show the actual criteria used for deriving the results shown, and I'm sure there must be a way to do this (I hope!). No MS Access parameters answers please. MS KB articles 209250 and 304353 are not what I am looking for. What I need to be able to do is show the "Is Null" or "Is Not Null" criteria on the report.
Thank you.

7 replies

by timwalsh

Maybe I'm just trying to oversimplify this.

I'm assuming that each report has it's own form. Why not just hardcode the criteria on the report form?

I would think that anyy auditor worth his/her salt would want to look at the underlying query if there is a question of whether the data is valid. Merely seeing the criteria used on a printed report (regardless of how the criteria got placed there) is not going to verify anything.

by benwagg

Thanks for the response. That is the work-around I have had to use. To answer your Why Not, though,
a. if you know the criteria was placed on the paper at report-time you can have a higher degree of confidence in it
b. once you have standard report forms, the easiest way to make new flavor of the report is to copy an existing one. But if someone is in a hurry or forgets to change the hard-text header, then the December reports may all get printed with November in the header.
c. we may quickly outgrow the ability to keep up with making sure headers are accurate. In a complex report, you may need variant reports, say, for salesman for each week of the year, or each region every month of the century. I don't want to try to guarantee I correctly hard-coded the report headers on that many reports. As soon as I do, we will have another logo change...
Name Inc....
Name and Son Inc....
Name and Sons Inc....
Name Brothers Inc....

I am attempting to make this as idiot-proof as I am able. Posting query criteria should be as easy as adding a header/footer with page numbers in Word, IMO. (Helloooo Microsoft...)

Would a subreport apply here? Instead of the report header, the subreport at the end could report what query ran for its parent report, with the query's criteria...?

by charles byrne

You might be able to get by with using
=[RecordSource].

It's what I use for dynamic reports. If it doesn't then you might want to try the following:

Insert a Module and Add the Following Code:

Function GetSQLQry(strQryName As String) As String
GetSQLQry = CurrentDb.QueryDefs(strQryName).SQL
End Function

In your Report, I would insert a text box in the
footer with the following code in it:

=GetSQLQry([RecordSource])

The output won't be pretty, but the SQL generated by the Query should show. It's a quick and dirty method (that's is why I put it in the footer). This should show the parameters supplied to the Query. Don't forget to make the textbox large. I have other functions that parse the sql to make it look pretty. You could do the same.

Hope this helps (works)
Charles

by benwagg

Thank you Charles. Although I did appreciate the other answers, I'm awarding all the points for your snippet of code. Your module worked exactly as advertised, and did precisely what I wanted, in pretty much the way I expected. Thanks for helping melook almost like I know what I'm doing here... :o)

It does lead to another question, though, so for another 100 points or so, what function or syntax will put [RecordSource].[SubQuery] in the footer?
Answer this one at: Access 2000 Report Criteria Followup

by john_wills

This is more than you need: I gave my user a choice of kinds of date. In the form:
Parameter.EventKind = Me.EventKind
Select Case Me.EventKind
Case "Birth"
KindOfDate = "DateOfBirth"
Case "Referral"
KindOfDate = "ReferralDate"
End Select
If IsNull(Me.BeginningDate) _
Then
Parameter.BeginningDate = 0
Else
Criterion = Criterion & " and [" & KindOfDate & "] >= DateValue('" & Me.BeginningDate & "')"
Parameter.BeginningDate = Me.BeginningDate
End If
If IsNull(Me.EndingDate) _
Then
Parameter.EndingDate = 0
Else
Criterion = Criterion _
& " and [" & KindOfDate & "] <= DateValue('" & Me.EndingDate & "')"
Parameter.EndingDate = Me.EndingDate
End If
DoCmd.OpenReport "ClientPriorHousing", acPreview, , Criterion
In the report:
If Parameter.BeginningDate = 0 _
Then
Specification.Caption = "For " & Parameter.EventKind & " Dates Through " _
& Parameter.EndingDate
Else
If Parameter.EndingDate =0 _
Then
Specification.Caption = "For " & Parameter.EventKind & " Dates From " _
& Parameter.BeginningDate
Else
Specification.Caption = "For " & Parameter.EventKind & " Dates From " _
& Parameter.BeginningDate _
& " Through " & Parameter.EndingDate
End If
End If
The parameter is in a code module. Specification is a label. The method "caption" does not appear in the list of possibilites when you write the code, but it does work.

by benwagg

Thank you for the response, but you are correct; that was more in-depth than I was look for with regard to user-selectable parameters. I will probaby keep this in mind for future projects, though, becasue it looks like you have a pretty useful classification selector there.

This discussion is closed to new replies.