How do you show query criteria in a report?

[userjf]

How do I show all of the criteria used in my query in a report? I would like to place this information at the top or end of the report so that once it is distributed to my development officers they can see the criteria & query name and request a refresh of same. I'd like to see:

1. Query Name
2. Any field that has query criteria:
[Field #7] Is Null
[Field #9] Like "E*"
3. Join & SQL information
4. Query properties
Unique values: Yes; Records Type: Snapshot; ODBC Timeout: 0

This information placed at the top or the bottom of a report would be invaluable whenever we process ad-hoc requests. It would tell what the previous criteria was, tables used and the SQL statement.

 

[gol4]

If all your reports are based on queries you can do this using the querydefs object

1 query name would be

me.recordsource me being the report

2 would be currentdb.querydefs(me.recordsource).parameters or parse the string if you did not set parameters

3 would be currentdb.querydefs(me.recordsource).SQL

may need to parse the string to get the join information

4 would be querydef.properties collection

hope that gets you started.

 

[gol4]

Another option for you would be to pull the information from the system tables get the id from msysobjects table then pull the info from the msysqueriestbl
attribute 5 = table names 6 = field names and 7 are your joins