Sum a DLookup

[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

 

[Ken Snell \(MVP\)]

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.

 

[Arlend Floyd]

Thanks you maid that was easy

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>

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

 

[Arlend Floyd]

How do I add % format to this?

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

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.

--

Ken Snell
<MS ACCESS MVP>

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

 

[Ken Snell \(MVP\)]

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.

 

[Arlend Floyd]

Thanks

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>

How do I add % format to this?

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

Arlend