Sum a DLookup

  • Thread starter Thread starter Arlend Floyd
  • Start date Start date
A

Arlend Floyd

I have a report with grouping based on RepName and it displays each reps
totals in [TxtTotals].

On that report I have another text box [TxtRate] with

=DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between
AmountCollectedLow And AmountCollectedHigh")

This gets my CommRate for each rep from a (Unrelated table) and this works
fine.

But I'm trying to get the average rate in the report footer

But when i use =Avg([TxtRate]) its looking for a parameter [TxtRate]

How can I do this without changing my query?

Thanks Arlend
 
The aggregate functions in Control Source expressions in a report require
that the expression contain fields from the report's RecordSource. You
cannot use a reference to a control in these functions.

You'll need to add a calculated field (name it TheRate) to the query that is
the report's RecordSource; set the expression for that calculated field to
the DLookup expression:
DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between
AmountCollectedLow And AmountCollectedHigh")

Then set the ControlSource of the txtRate textbox to TheRate, and use
TheRate in the Avg function expression in the report footer's textbox.
 
Thanks you maid that was easy

Ken Snell (MVP) said:
The aggregate functions in Control Source expressions in a report require
that the expression contain fields from the report's RecordSource. You
cannot use a reference to a control in these functions.

You'll need to add a calculated field (name it TheRate) to the query that is
the report's RecordSource; set the expression for that calculated field to
the DLookup expression:
DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between
AmountCollectedLow And AmountCollectedHigh")

Then set the ControlSource of the txtRate textbox to TheRate, and use
TheRate in the Avg function expression in the report footer's textbox.

--

Ken Snell
<MS ACCESS MVP>



Arlend Floyd said:
I have a report with grouping based on RepName and it displays each reps
totals in [TxtTotals].

On that report I have another text box [TxtRate] with

=DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between
AmountCollectedLow And AmountCollectedHigh")

This gets my CommRate for each rep from a (Unrelated table) and this works
fine.

But I'm trying to get the average rate in the report footer

But when i use =Avg([TxtRate]) its looking for a parameter [TxtRate]

How can I do this without changing my query?

Thanks Arlend
Click to expand...
Click to expand...
 
How do I add % format to this?

Rate: DLookUp("CommRate","TblCommissionRates",[Sum] & " Between
AmountCollectedLow And AmountCollectedHigh")

Arlend

Ken Snell (MVP) said:
The aggregate functions in Control Source expressions in a report require
that the expression contain fields from the report's RecordSource. You
cannot use a reference to a control in these functions.

You'll need to add a calculated field (name it TheRate) to the query that is
the report's RecordSource; set the expression for that calculated field to
the DLookup expression:
DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between
AmountCollectedLow And AmountCollectedHigh")

Then set the ControlSource of the txtRate textbox to TheRate, and use
TheRate in the Avg function expression in the report footer's textbox.

--

Ken Snell
<MS ACCESS MVP>



Arlend Floyd said:
I have a report with grouping based on RepName and it displays each reps
totals in [TxtTotals].

On that report I have another text box [TxtRate] with

=DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between
AmountCollectedLow And AmountCollectedHigh")

This gets my CommRate for each rep from a (Unrelated table) and this works
fine.

But I'm trying to get the average rate in the report footer

But when i use =Avg([TxtRate]) its looking for a parameter [TxtRate]

How can I do this without changing my query?

Thanks Arlend
Click to expand...
Click to expand...
 
You can use the Format function to do that in the query:

Rate: Format(DLookUp("CommRate","TblCommissionRates",[Sum] & " Between
AmountCollectedLow And AmountCollectedHigh"), "Percent")

Or you can use the Format property of the textbox in the report to do this.
Set the Format property of that textbox to Percent.
 
Thanks

Ken Snell (MVP) said:
You can use the Format function to do that in the query:

Rate: Format(DLookUp("CommRate","TblCommissionRates",[Sum] & " Between
AmountCollectedLow And AmountCollectedHigh"), "Percent")

Or you can use the Format property of the textbox in the report to do this.
Set the Format property of that textbox to Percent.
--

Ken Snell
<MS ACCESS MVP>

Arlend Floyd said:
How do I add % format to this?

Rate: DLookUp("CommRate","TblCommissionRates",[Sum] & " Between
AmountCollectedLow And AmountCollectedHigh")

Arlend
Click to expand...
Click to expand...
 
Back
Top