lookup values in subreport and display result

[Ron5440]

I have a report that shows whether of a bunch of batch jobs ran successfully
or failed. I have a calculated (OkNg) field that determines if the jobs ran
successfully and returns either "OK" or "Fail". I want a new control on the
report to look at all of the values in the 'OkNg' field and return 'Fail' if
any one of the jobs failed, or 'Ok' if they were all successfull.

 

[Duane Hookom]

This depends on the calculation of the OkNg field. Is it in the query or a
calculation in the control source? If it is in the query, you should be able
to use a control source in the Report Header or Footer section like:
=Min(OkNg)

 

[Ron5440]

Duane,
Yes it is in the SQL statement which is the record source for the subreport.
The info that I am gathering is broken down into 3 different plants using
sorting and grouping on the main report. I want to display 'Ok' or 'Fail' in
a single field for each plant. I had a 'Plant' header, I added the 'Plant'
footer and dropped in a control with the =Min(OkNg) and when I open the
report I get a parameter box looking for a OkNg value.

 

[Duane Hookom]

If you have a field/column in your report's record source named OkNg then you
won't get prompted for it in the report.

I wasn't aware there were subreports involved based on your original
question. Where is the OkNg field and where do you want to see the "new
control"?

 

[Ron5440]

The OkNg field is in the subreport, it is a calculated field in the record
source. I want to see the new control on the main report. I added the
following to the 'Plant' footer (actually I tried it on all of the footers)
=Min([rptRRTimeStamp]![srpRRBatchJobTimeTrackingP1]![OkNg])

 

[Duane Hookom]

You can use the report footer in the subreport with a

Name: txtOkNg
Control source:=Min(OkNg)

Then in the main report, you can reference this control with a control
source of
=srpRRBatchJobTimeTrackingP1.Report!txtOkNg

The other solution is adding another table to your main report record source
but grouping so you don't get extra detail records. Add a column to the main
report's record source query that returns Min(OkNg).

--
Duane Hookom
Microsoft Access MVP

The OkNg field is in the subreport, it is a calculated field in the record
source. I want to see the new control on the main report. I added the
following to the 'Plant' footer (actually I tried it on all of the footers)
=Min([rptRRTimeStamp]![srpRRBatchJobTimeTrackingP1]![OkNg])

If you have a field/column in your report's record source named OkNg then you
won't get prompted for it in the report.

I wasn't aware there were subreports involved based on your original
question. Where is the OkNg field and where do you want to see the "new
control"?

 

[Ron5440]

Duane,
Fixed, my issue was that the OkNg field was a calculated field based on
another calculated field. I moved the calculated fields to the original qry
so when they came into the srp they were values. I guess that allowed the
calculated field in the footer to process the min function.

Thanks,
Ron

You can use the report footer in the subreport with a

Name: txtOkNg
Control source:=Min(OkNg)

Then in the main report, you can reference this control with a control
source of
=srpRRBatchJobTimeTrackingP1.Report!txtOkNg

The other solution is adding another table to your main report record source
but grouping so you don't get extra detail records. Add a column to the main
report's record source query that returns Min(OkNg).

--
Duane Hookom
Microsoft Access MVP

The OkNg field is in the subreport, it is a calculated field in the record
source. I want to see the new control on the main report. I added the
following to the 'Plant' footer (actually I tried it on all of the footers)
=Min([rptRRTimeStamp]![srpRRBatchJobTimeTrackingP1]![OkNg])

If you have a field/column in your report's record source named OkNg then you
won't get prompted for it in the report.

I wasn't aware there were subreports involved based on your original
question. Where is the OkNg field and where do you want to see the "new
control"?
--
Duane Hookom
Microsoft Access MVP


:

Duane,
Yes it is in the SQL statement which is the record source for the subreport.
The info that I am gathering is broken down into 3 different plants using
sorting and grouping on the main report. I want to display 'Ok' or 'Fail' in
a single field for each plant. I had a 'Plant' header, I added the 'Plant'
footer and dropped in a control with the =Min(OkNg) and when I open the
report I get a parameter box looking for a OkNg value.


:

This depends on the calculation of the OkNg field. Is it in the query or a
calculation in the control source? If it is in the query, you should be able
to use a control source in the Report Header or Footer section like:
=Min(OkNg)

--
Duane Hookom
Microsoft Access MVP


:

I have a report that shows whether of a bunch of batch jobs ran successfully
or failed. I have a calculated (OkNg) field that determines if the jobs ran
successfully and returns either "OK" or "Fail". I want a new control on the
report to look at all of the values in the 'OkNg' field and return 'Fail' if
any one of the jobs failed, or 'Ok' if they were all successfull.

 

[Duane Hookom]

That's why I asked earlier if the field was calc'd in the query or in the
report.

Can we assume your issue is resolved?

--
Duane Hookom
Microsoft Access MVP

Duane,
Fixed, my issue was that the OkNg field was a calculated field based on
another calculated field. I moved the calculated fields to the original qry
so when they came into the srp they were values. I guess that allowed the
calculated field in the footer to process the min function.

Thanks,
Ron

You can use the report footer in the subreport with a

Name: txtOkNg
Control source:=Min(OkNg)

Then in the main report, you can reference this control with a control
source of
=srpRRBatchJobTimeTrackingP1.Report!txtOkNg

The other solution is adding another table to your main report record source
but grouping so you don't get extra detail records. Add a column to the main
report's record source query that returns Min(OkNg).

--
Duane Hookom
Microsoft Access MVP

The OkNg field is in the subreport, it is a calculated field in the record
source. I want to see the new control on the main report. I added the
following to the 'Plant' footer (actually I tried it on all of the footers)
=Min([rptRRTimeStamp]![srpRRBatchJobTimeTrackingP1]![OkNg])

:

If you have a field/column in your report's record source named OkNg then you
won't get prompted for it in the report.

I wasn't aware there were subreports involved based on your original
question. Where is the OkNg field and where do you want to see the "new
control"?
--
Duane Hookom
Microsoft Access MVP


:

Duane,
Yes it is in the SQL statement which is the record source for the subreport.
The info that I am gathering is broken down into 3 different plants using
sorting and grouping on the main report. I want to display 'Ok' or 'Fail' in
a single field for each plant. I had a 'Plant' header, I added the 'Plant'
footer and dropped in a control with the =Min(OkNg) and when I open the
report I get a parameter box looking for a OkNg value.


:

This depends on the calculation of the OkNg field. Is it in the query or a
calculation in the control source? If it is in the query, you should be able
to use a control source in the Report Header or Footer section like:
=Min(OkNg)

--
Duane Hookom
Microsoft Access MVP


:

I have a report that shows whether of a bunch of batch jobs ran successfully
or failed. I have a calculated (OkNg) field that determines if the jobs ran
successfully and returns either "OK" or "Fail". I want a new control on the
report to look at all of the values in the 'OkNg' field and return 'Fail' if
any one of the jobs failed, or 'Ok' if they were all successfull.