Using value in header in detail section formula 1

[BrianLe]

In AC2000, how can I use a value in the Header section of a report in the formula for a value in the Detail section of the report?

The tblPlanning includes fields Activity and BurnRate. For each record of the plan, there is an Activity, but since BurnRate is constant for all the phases of the plan it is stored only in the first record of the plan.

In my report I would like to show the BurnRate in the Header, and for each phase of the plan I would like to use the BurnRate in a formula that is based on the Activity (FillRate = AddRate-BurnRate)

My qryPlanningReport returns the values "AddRate" and "BurnRate". I can get the report header to show the "BurnRate" and the detail section to show the AddRate for each Activity. However, the "FillRate" only shows up for the first line of the detail section of the report. I assume this happens because BurnRate is only in the first record of the plan.

If I could get the qryPlanningReport to populate the BurnRate in each record, I think I could get the report to work.

Is that the approach to take, or is there a better way?

Thanks,

Brian

 

[dhookom]

Can you provide some sample records and desired output? Your statement "first line of the detail section of the report" suggests a specific sort order. Can you confirm this?

Duane
Hook'D on Access
MS Access MVP

 

[BrianLe]

The tables are set up similar to this.

tblPlanning

PlanningID TrainID PhaseNo ActivityNo BurnRate
Autonum 2 1 2 200
2 2 3 Null
2 3 2 Null
2 4 4 Null

tblActivity

ActivityID ActivityNo AddRate
Autonum 1 100
2 400
3 200
4 500

The "FillRate = AddRate-BurnRate

where the BurnRate for each phase is the same as the "BurnRate" in the "PhaseNo" = 1

I would like the report to show the following for the selected TrainID

PhaseNo ActivityNo FillRate
1 2 -100 (=100-200)
2 3 0 (=200-200)
3 2 -100 (=100-200)
4 4 300 (=500-200)

Thanks,

Brian

 

[dhookom]

Place a text box in the Report Header section:
Name: txtMaxBurnRate
Control Source: =Max([BurnRate])

Then in the detail section, use:
Control Source: =[AddRate]-[txtMaxBurnRate]

Duane
Hook'D on Access
MS Access MVP

 

[BrianLe]

The =Max function gives me an #Error in the txbMaxBurnRate when using AC2003, but is OK when using AC97.

Do I need to register an ActiveX control on the AC2003 machine?

Thanks,

Brian

 

[dhookom]

Did you place the text box in the Report Header or the Page Header?

Duane
Hook'D on Access
MS Access MVP

 

[BrianLe]

You hit the nail on the head. I had it in the Page Header.

Thank you very much.

Brian

 

[dhookom]

This has been the problem in two of the most recent Reports forum questions where I have stated early on to use the "Report Footer" and the poster didn't understand and had tried to use the "Page Footer".

I think Microsoft should add a large non-printing banner in the Page Header and Footer sections that states [red]"YOU CAN'T PUT SUM EXPRESSIONS HERE![/red]"

Duane
Hook'D on Access
MS Access MVP