Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query Criteria printed in Report 1
- Thread starter Knicks
- Start date
- Thread starter
- #3
Lets say you have a query that is the data source for your report, then you have 3 fields in the report
City, State, Profession
Then you have the criteria line for City being "albany" and the criteria line for state being "New York" in the query.
What I would like to see on my report is City=Albany, State=New York
and I would like them pulled from the criteria line of the query. This way when I run reports I could see what underlying query criteria generated the data seen in the report
City, State, Profession
Then you have the criteria line for City being "albany" and the criteria line for state being "New York" in the query.
What I would like to see on my report is City=Albany, State=New York
and I would like them pulled from the criteria line of the query. This way when I run reports I could see what underlying query criteria generated the data seen in the report
Knicks
I'm not sure where the query is getting it's "State" and "City" and "Profession" criteria, but have you consider driving the process from a form?
If you drive it from a form, what you want is easier to do, because then you don't have to make a bunch of different queries, or have parameters to complete each time.
Here's how you would do what I am suggesting.
1. Create an unbound form. Let's call it frmSelectCriteria. The form would have 3 text boxes. Let's call one txtCity, another txtState, and the third txtProfession. You would also have a command button that, when pressed, would either Preview or Print the report.
2. In your query, in criteria row for the City column, put
Forms!frmSelectCriteria!txtCity
In the criteria row for the State column, put
Forms!frmSelectCriteria!txtState
In the criteria row for the Profession, put
Forms!frmSelectCriteria!txtProfession
3. In your Report Header, that has its Record Source as the query you are using, put an unbound text box that has something such as the following expression...
= "This report covers all people in the city of " & Forms!frmSelectCriteria!txtCity & ", " & Forms!frmSelectCriteria!txtState & " whose profession is " & Forms!frmSelectCriteria!txtProfession
You only need to build this form once. And you only need 1 query and 1 report to run this process.
You start by opening the form, enter the criteria in the 3 text boxes, press the command button and it's done.
Tom
I'm not sure where the query is getting it's "State" and "City" and "Profession" criteria, but have you consider driving the process from a form?
If you drive it from a form, what you want is easier to do, because then you don't have to make a bunch of different queries, or have parameters to complete each time.
Here's how you would do what I am suggesting.
1. Create an unbound form. Let's call it frmSelectCriteria. The form would have 3 text boxes. Let's call one txtCity, another txtState, and the third txtProfession. You would also have a command button that, when pressed, would either Preview or Print the report.
2. In your query, in criteria row for the City column, put
Forms!frmSelectCriteria!txtCity
In the criteria row for the State column, put
Forms!frmSelectCriteria!txtState
In the criteria row for the Profession, put
Forms!frmSelectCriteria!txtProfession
3. In your Report Header, that has its Record Source as the query you are using, put an unbound text box that has something such as the following expression...
= "This report covers all people in the city of " & Forms!frmSelectCriteria!txtCity & ", " & Forms!frmSelectCriteria!txtState & " whose profession is " & Forms!frmSelectCriteria!txtProfession
You only need to build this form once. And you only need 1 query and 1 report to run this process.
You start by opening the form, enter the criteria in the 3 text boxes, press the command button and it's done.
Tom
Knicks
I hope you will try what I suggested.
If for some reason you really don't want to do it that way, you can put the criteria in the Report Header this way. I'll use the State as an example.
Put an unbound text box that has the following expression
="State = " & [State]
This presumes, of course, that the field is called State. If it's not, then you would change [State] to whatever the field is called.
You can do the same with the [City] field.
Tom
I hope you will try what I suggested.
If for some reason you really don't want to do it that way, you can put the criteria in the Report Header this way. I'll use the State as an example.
Put an unbound text box that has the following expression
="State = " & [State]
This presumes, of course, that the field is called State. If it's not, then you would change [State] to whatever the field is called.
You can do the same with the [City] field.
Tom
- Thread starter
- #6
Yeah, I know how to do it that way.
That was just a simple example. Actually my query is much more complex with data that will not change often. Its because it won't change often (the underlying query) that I want to make sure when I run it I know what its being run from. I suppose I could make the input form and have them all default to my criteria (what I would of had on the query criteria line) and then have that data appear on my report --- I could even have the darn form open hidden when I run the report and never even see it to make the criteria appear.
But isn't there a simpler cleaner technique for "grabbing" what you have on the criteria line of your quert and insert it into your report??
That was just a simple example. Actually my query is much more complex with data that will not change often. Its because it won't change often (the underlying query) that I want to make sure when I run it I know what its being run from. I suppose I could make the input form and have them all default to my criteria (what I would of had on the query criteria line) and then have that data appear on my report --- I could even have the darn form open hidden when I run the report and never even see it to make the criteria appear.
But isn't there a simpler cleaner technique for "grabbing" what you have on the criteria line of your quert and insert it into your report??
Knicks
Then, from my second reply...since, for example, the [State] field will be in the Report's field list, and since the only state involved in this instance is New York, and the only City involved is Albany, then using the expression that I suggested, in an unbound text box in the Report Header, would work...
= "City = " [City] & " State = " & [State]
Tom
Then, from my second reply...since, for example, the [State] field will be in the Report's field list, and since the only state involved in this instance is New York, and the only City involved is Albany, then using the expression that I suggested, in an unbound text box in the Report Header, would work...
= "City = " [City] & " State = " & [State]
Tom
I tried posting this several hours ago but unfortunately there was some proxy problem at my end (I think)
Well, if you set the report's recordsource to an sql statement, simply extract the criteria from Me.RecordSource and display it on an unbound text box in the section's Format event.
If you base the report on a query, then pull out the query SQL by making use of DAO or ADOX. DAO is probably easiest since it's built in.
For this example, you will need an unbound text box called txtCr in the ReportHeader section.
The example will display the SQL statement from the start of the criteria to the end of the statement. You can modify it further for your needs (i.e. extract only the criteria which is between the WHERE and an optional ORDER BY, GROUP BY or lots of other possible clauses)
Well, if you set the report's recordsource to an sql statement, simply extract the criteria from Me.RecordSource and display it on an unbound text box in the section's Format event.
If you base the report on a query, then pull out the query SQL by making use of DAO or ADOX. DAO is probably easiest since it's built in.
For this example, you will need an unbound text box called txtCr in the ReportHeader section.
The example will display the SQL statement from the start of the criteria to the end of the statement. You can modify it further for your needs (i.e. extract only the criteria which is between the WHERE and an optional ORDER BY, GROUP BY or lots of other possible clauses)
Code:
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim pos As Long
Dim sSql As String
Dim qry As DAO.QueryDef
sSql = Me.RecordSource
On Error Resume Next
Set qry = CurrentDb.QueryDefs(Me.RecordSource)
On Error GoTo 0
If Not (qry Is Nothing) Then
sSql = qry.SQL
End If
pos = InStr(sSql, "WHERE")
If pos > 0 Then
txtCr.Value = Mid(sSql, pos + 6)
Else
txtCr.Value = ""
End If
End Sub- Thread starter
- #9
PcLewis
Yes, this is the direction I'm trying to head in, so I can see what criteria went into my report outcome.
I have tried your technique and it looks quite promising. I do get a compile error though with
Dim qry As DAO.QueryDef
Something about user defined error......
Yes, this is the direction I'm trying to head in, so I can see what criteria went into my report outcome.
I have tried your technique and it looks quite promising. I do get a compile error though with
Dim qry As DAO.QueryDef
Something about user defined error......
-
1
- #10
- Thread starter
- #11
Thanx,
I got it to work. It would be cooler if it just contains the "where" portion, but the whole SQL structure definitely has value, as in this case it shows the fact that the underlying query is build off of another query.
If you know any tricks to parse the "where" portion that would be cool, but this is nice too.
Thanx!
I got it to work. It would be cooler if it just contains the "where" portion, but the whole SQL structure definitely has value, as in this case it shows the fact that the underlying query is build off of another query.
If you know any tricks to parse the "where" portion that would be cool, but this is nice too.
Thanx!
- Status
Similar threads
- Locked
- Question
