Calculation in text box

[Guest]

Hello to all-- I was told that you can calculate data in a text box in
Access? Is there some where I can get mathematical calculation to use in
Access? Example
If I wanted to get an average of three samples entered in three separate
boxes in my form setup and I have built a text box to display the average.
What and how do I need to enter that calculation? I have tried the Event
Builder however; I’m not having much luck with the outcome. Can some one
display a simple Average calculation for three samples? Exp:
=average((sample 1)+(sample 2)+(sample 3)) is this correct?
Thanks -- for you reply -- EM

 

[Guest]

In this case you just need to divide the sum of the three values by 3:

=([Sample1]+[Sample2]+[Sample3])/3

If any of the text boxes could be Null (empty of any value) then use the Nz
function to return a zero in place of any Null:

=(Nz([Sample1],0)+Nz([Sample2],0)+Nz([Sample3],0))/3

There is an AVG operator, but that is used in a query for getting the
average value of values in a column, not for averaging a value list. There
is also a DAvg function which operates similarly in VBA by averaging the
values from a set of rows (the domain).

It would be quite easy to write a little function for averaging a value list
of varying number of values passed into the function as a parameter array,
e.g.

Public Function AvgValue(ParamArray aVals() As Variant)

Dim var As Variant
Dim intValCount As Integer
Dim dblValTotal As Double

For Each var In aVals
dblValTotal = dblValTotal + Nz(var, 0)
intValCount = intValCount + 1
Next var

AvgValue = dblValTotal / intValCount

End Function

By putting the above function in a standard module you can call it anywhere
in the database, e.g. in your case as the ControlSource of the unbound text
box on the form:

= AvgValue([Sample1],[Sample2],[Sample3])

Ken Sheridan
Stafford, England

 

[Guest]

Thank you Ken your rendition will assist me greatly! Have a great week.

In this case you just need to divide the sum of the three values by 3:

=([Sample1]+[Sample2]+[Sample3])/3

If any of the text boxes could be Null (empty of any value) then use the Nz
function to return a zero in place of any Null:

=(Nz([Sample1],0)+Nz([Sample2],0)+Nz([Sample3],0))/3

There is an AVG operator, but that is used in a query for getting the
average value of values in a column, not for averaging a value list. There
is also a DAvg function which operates similarly in VBA by averaging the
values from a set of rows (the domain).

It would be quite easy to write a little function for averaging a value list
of varying number of values passed into the function as a parameter array,
e.g.

Public Function AvgValue(ParamArray aVals() As Variant)

Dim var As Variant
Dim intValCount As Integer
Dim dblValTotal As Double

For Each var In aVals
dblValTotal = dblValTotal + Nz(var, 0)
intValCount = intValCount + 1
Next var

AvgValue = dblValTotal / intValCount

End Function

By putting the above function in a standard module you can call it anywhere
in the database, e.g. in your case as the ControlSource of the unbound text
box on the form:

= AvgValue([Sample1],[Sample2],[Sample3])

Ken Sheridan
Stafford, England

Hello to all-- I was told that you can calculate data in a text box in
Access? Is there some where I can get mathematical calculation to use in
Access? Example
If I wanted to get an average of three samples entered in three separate
boxes in my form setup and I have built a text box to display the average.
What and how do I need to enter that calculation? I have tried the Event
Builder however; I’m not having much luck with the outcome. Can some one
display a simple Average calculation for three samples? Exp:
=average((sample 1)+(sample 2)+(sample 3)) is this correct?
Thanks -- for you reply -- EM

 

[Maverick]

This is a good function, however, it does not calculate a true average if
there are any fields that are blank. I have adapted the function to allow for
blank (i.e. you have 7 field but only put values in 6 of them... the original
function would still divide by 7 and the modified function would divide by 6).

-----------------------------------------------------------------------------------------------

Public Function AvgValue(ParamArray aVals() As Variant)

Dim var As Variant
Dim intValCount As Integer
Dim dblValTotal As Double

For Each var In aVals
dblValTotal = dblValTotal + Nz(var, 0)
If Nz(var, "") = "" Then
intValCount = intValCount
Else
intValCount = intValCount + 1
End If
Next var

AvgValue = dblValTotal / intValCount

End Function

------------------------------------------------------------------------------------------------

In this case you just need to divide the sum of the three values by 3:

=([Sample1]+[Sample2]+[Sample3])/3

If any of the text boxes could be Null (empty of any value) then use the Nz
function to return a zero in place of any Null:

=(Nz([Sample1],0)+Nz([Sample2],0)+Nz([Sample3],0))/3

There is an AVG operator, but that is used in a query for getting the
average value of values in a column, not for averaging a value list. There
is also a DAvg function which operates similarly in VBA by averaging the
values from a set of rows (the domain).

It would be quite easy to write a little function for averaging a value list
of varying number of values passed into the function as a parameter array,
e.g.

Public Function AvgValue(ParamArray aVals() As Variant)

Dim var As Variant
Dim intValCount As Integer
Dim dblValTotal As Double

For Each var In aVals
dblValTotal = dblValTotal + Nz(var, 0)
intValCount = intValCount + 1
Next var

AvgValue = dblValTotal / intValCount

End Function

By putting the above function in a standard module you can call it anywhere
in the database, e.g. in your case as the ControlSource of the unbound text
box on the form:

= AvgValue([Sample1],[Sample2],[Sample3])

Ken Sheridan
Stafford, England

Hello to all-- I was told that you can calculate data in a text box in
Access? Is there some where I can get mathematical calculation to use in
Access? Example
If I wanted to get an average of three samples entered in three separate
boxes in my form setup and I have built a text box to display the average.
What and how do I need to enter that calculation? I have tried the Event
Builder however; I’m not having much luck with the outcome. Can some one
display a simple Average calculation for three samples? Exp:
=average((sample 1)+(sample 2)+(sample 3)) is this correct?
Thanks -- for you reply -- EM

 

[Whitney]

I have a simple calculation where all fields will be populated and no zeros.
So I'm thinking this will work for me: =([Sample1]+[Sample2]+[Sample3])/3
I'm using a text box to show the average, where do I put this formula?

In this case you just need to divide the sum of the three values by 3:

=([Sample1]+[Sample2]+[Sample3])/3

If any of the text boxes could be Null (empty of any value) then use the Nz
function to return a zero in place of any Null:

=(Nz([Sample1],0)+Nz([Sample2],0)+Nz([Sample3],0))/3

There is an AVG operator, but that is used in a query for getting the
average value of values in a column, not for averaging a value list. There
is also a DAvg function which operates similarly in VBA by averaging the
values from a set of rows (the domain).

It would be quite easy to write a little function for averaging a value list
of varying number of values passed into the function as a parameter array,
e.g.

Public Function AvgValue(ParamArray aVals() As Variant)

Dim var As Variant
Dim intValCount As Integer
Dim dblValTotal As Double

For Each var In aVals
dblValTotal = dblValTotal + Nz(var, 0)
intValCount = intValCount + 1
Next var

AvgValue = dblValTotal / intValCount

End Function

By putting the above function in a standard module you can call it anywhere
in the database, e.g. in your case as the ControlSource of the unbound text
box on the form:

= AvgValue([Sample1],[Sample2],[Sample3])

Ken Sheridan
Stafford, England

Hello to all-- I was told that you can calculate data in a text box in
Access? Is there some where I can get mathematical calculation to use in
Access? Example
If I wanted to get an average of three samples entered in three separate
boxes in my form setup and I have built a text box to display the average.
What and how do I need to enter that calculation? I have tried the Event
Builder however; I’m not having much luck with the outcome. Can some one
display a simple Average calculation for three samples? Exp:
=average((sample 1)+(sample 2)+(sample 3)) is this correct?
Thanks -- for you reply -- EM

 

[Maverick]

You would place the formula in the Control Source for the textbox.
--

HTH

Don''''t forget to rate the post if it was helpful!

Please reply to newsgroup only, so that others may benefit as well.

I have a simple calculation where all fields will be populated and no zeros.
So I'm thinking this will work for me: =([Sample1]+[Sample2]+[Sample3])/3
I'm using a text box to show the average, where do I put this formula?

In this case you just need to divide the sum of the three values by 3:

=([Sample1]+[Sample2]+[Sample3])/3

If any of the text boxes could be Null (empty of any value) then use the Nz
function to return a zero in place of any Null:

=(Nz([Sample1],0)+Nz([Sample2],0)+Nz([Sample3],0))/3

There is an AVG operator, but that is used in a query for getting the
average value of values in a column, not for averaging a value list. There
is also a DAvg function which operates similarly in VBA by averaging the
values from a set of rows (the domain).

It would be quite easy to write a little function for averaging a value list
of varying number of values passed into the function as a parameter array,
e.g.

Public Function AvgValue(ParamArray aVals() As Variant)

Dim var As Variant
Dim intValCount As Integer
Dim dblValTotal As Double

For Each var In aVals
dblValTotal = dblValTotal + Nz(var, 0)
intValCount = intValCount + 1
Next var

AvgValue = dblValTotal / intValCount

End Function

By putting the above function in a standard module you can call it anywhere
in the database, e.g. in your case as the ControlSource of the unbound text
box on the form:

= AvgValue([Sample1],[Sample2],[Sample3])

Ken Sheridan
Stafford, England

Hello to all-- I was told that you can calculate data in a text box in
Access? Is there some where I can get mathematical calculation to use in
Access? Example
If I wanted to get an average of three samples entered in three separate
boxes in my form setup and I have built a text box to display the average.
What and how do I need to enter that calculation? I have tried the Event
Builder however; I’m not having much luck with the outcome. Can some one
display a simple Average calculation for three samples? Exp:
=average((sample 1)+(sample 2)+(sample 3)) is this correct?
Thanks -- for you reply -- EM