Aggregate Functions on Form

[Jen]

Hi All,

I can't figure out why I'm getting the #ERROR message.

I have a txtBallVar textbox in the details section of my
form. In the form header I have another textbox -
txtLotMeanDiam. I would like to take all of the values in
the detail section and get an average. In the
txtLotMeanDiam controlsource property I have this: =Avg
(txtLotMeanDiam.value) and I've also tried this: =Avg
(txtLotMeanDiam) to no avail. I keep getting the #ERROR
message. I also have other aggregate functions (min, max
and sum) I'm trying to do but getting the same error.
Another thing I've tried to do is put the aggregate
textboxes in the form footer section and then tried to put
them right in the detail section.....same #ERROR message.

Can anyone tell me where I'm going wrong or if this is
some kind of bug?

Thanks,
Jen

 

[Mike S.]

If I understand you correctly, you have continuous form with txtBallVar
bound to some value (say BallDiam) and u need to avg BallDiam in
txtLotMeanDiam.

If so, then the ControlSource for txtLotMeanDiam should use the field name
that txtBallVar is bound to:

=Avg([BallDiam])

 

[Jen]

Oh, if only it were that easy. Actually the txtBallVar is
not bound but is also a calculated field, I think this is
where I'm having the trouble. Any other suggestions? I
really don't want to have to store the txtBallVar in the
table.

Thanks so much!

Jen

-----Original Message-----
If I understand you correctly, you have continuous form with txtBallVar
bound to some value (say BallDiam) and u need to avg BallDiam in
txtLotMeanDiam.

If so, then the ControlSource for txtLotMeanDiam should use the field name
that txtBallVar is bound to:

=Avg([BallDiam])

--
Mike S.
Optimal Systems www.oscorp.com
--

Hi All,

I can't figure out why I'm getting the #ERROR message.

I have a txtBallVar textbox in the details section of my
form. In the form header I have another textbox -
txtLotMeanDiam. I would like to take all of the values in
the detail section and get an average. In the
txtLotMeanDiam controlsource property I have this: =Avg
(txtLotMeanDiam.value) and I've also tried this: =Avg
(txtLotMeanDiam) to no avail. I keep getting the #ERROR
message. I also have other aggregate functions (min, max
and sum) I'm trying to do but getting the same error.
Another thing I've tried to do is put the aggregate
textboxes in the form footer section and then tried to put
them right in the detail section.....same #ERROR message.

Can anyone tell me where I'm going wrong or if this is
some kind of bug?

Thanks,
Jen

.

 

[Mike S.]

It's never easy. You cannot agg calculated fields in forms.

How about changing the form's RecordSource to a query. Calculate the value
of txtBallVar as a field:

Me.RecordSource = "select *, [SomeField]*100 AS BallVar from ..."

txtBallVar then would have BallVar as its source and txtLotMeanDiam would
then be Avg([BallVar])

--
Mike S.
Optimal Systems www.oscorp.com
--

Oh, if only it were that easy. Actually the txtBallVar is
not bound but is also a calculated field, I think this is
where I'm having the trouble. Any other suggestions? I
really don't want to have to store the txtBallVar in the
table.

Thanks so much!

Jen

-----Original Message-----
If I understand you correctly, you have continuous form with txtBallVar
bound to some value (say BallDiam) and u need to avg BallDiam in
txtLotMeanDiam.

If so, then the ControlSource for txtLotMeanDiam should use the field name
that txtBallVar is bound to:

=Avg([BallDiam])

--
Mike S.
Optimal Systems www.oscorp.com
--

Hi All,

I can't figure out why I'm getting the #ERROR message.

I have a txtBallVar textbox in the details section of my
form. In the form header I have another textbox -
txtLotMeanDiam. I would like to take all of the values in
the detail section and get an average. In the
txtLotMeanDiam controlsource property I have this: =Avg
(txtLotMeanDiam.value) and I've also tried this: =Avg
(txtLotMeanDiam) to no avail. I keep getting the #ERROR
message. I also have other aggregate functions (min, max
and sum) I'm trying to do but getting the same error.
Another thing I've tried to do is put the aggregate
textboxes in the form footer section and then tried to put
them right in the detail section.....same #ERROR message.

Can anyone tell me where I'm going wrong or if this is
some kind of bug?

Thanks,
Jen

.

 

[Jen]

Great minds think alike...that's precisely what I did.
Works like a charm. Thanks for the help!

Jen

-----Original Message-----
It's never easy. You cannot agg calculated fields in forms.

How about changing the form's RecordSource to a query. Calculate the value
of txtBallVar as a field:

Me.RecordSource = "select *, [SomeField]*100 AS BallVar from ..."

txtBallVar then would have BallVar as its source and txtLotMeanDiam would
then be Avg([BallVar])

--
Mike S.
Optimal Systems www.oscorp.com
--

Oh, if only it were that easy. Actually the txtBallVar is
not bound but is also a calculated field, I think this is
where I'm having the trouble. Any other suggestions? I
really don't want to have to store the txtBallVar in the
table.

Thanks so much!

Jen

-----Original Message-----
If I understand you correctly, you have continuous form with txtBallVar
bound to some value (say BallDiam) and u need to avg BallDiam in
txtLotMeanDiam.

If so, then the ControlSource for txtLotMeanDiam should use the field name
that txtBallVar is bound to:

=Avg([BallDiam])

--
Mike S.
Optimal Systems www.oscorp.com
--
Hi All,

I can't figure out why I'm getting the #ERROR message.

I have a txtBallVar textbox in the details section of my
form. In the form header I have another textbox -
txtLotMeanDiam. I would like to take all of the

values
in

the detail section and get an average. In the
txtLotMeanDiam controlsource property I have this: =Avg
(txtLotMeanDiam.value) and I've also tried this: =Avg
(txtLotMeanDiam) to no avail. I keep getting the #ERROR
message. I also have other aggregate functions (min, max
and sum) I'm trying to do but getting the same error.
Another thing I've tried to do is put the aggregate
textboxes in the form footer section and then tried

to
put

them right in the detail section.....same #ERROR message.

Can anyone tell me where I'm going wrong or if this is
some kind of bug?

Thanks,
Jen



.

.