Averaging separate fields in a form, excluding blanks

[Guest]

Although this may seem trivial, I've been struggling all day w/ it. I have 3
percentage fileds on a form. I would like to gather the average of the
percentage fields that contain actual data.

Field 1 = 6.0%
Field 2 = 7.0%
Field 3 = blank

Traditional math tells you (6% + 7% + 0%) / 3 = 4.33%. But that's not the
case here. I only have two fields populated, since the third field is left
blank, so the average should be (6% + 7%) / 2 = 6.5%

Question is: how can I get the average of 3 fields, where only one or two
of the fields may contain actual data?

I tried summing all the fields and then using a count function baked in, but
I'm having no luck. As you know, I can't use traditional math to divide by
the total number of fields because not all fields may contain data.

Any advice/help is greatly appreciated. Happy holidays.

Frank

 

[Ron Weiner]

How about

Assuming you want to fet the average of all rows of one field in your table
SELECT Avg(YourField) AS AvgOfYourField FROM YourTable

This will ignore Rows that are Null

Ron W

 

[Guest]

Ron,

I don't want to grab the average of all rows of one field. I want to grab
the average of 3 fields, on the same record. I have one record with 4
different fields. The first 3 fields represent actual input of values. The
4th field represents the average of the first three fields (no data entry
allowed). I want the 4th field to contain the average of the 3 fields, and
to not include blank entry fields in the average calculation. (hence my
example below)

This would then be repeated for each record in the table. So in short, I
want to grab the average of 3 different fields for the same record, and
display that result in the 4th field. Thanks.

Frank

P.S. Let me know if there's anything special about percentages that may
hinder this calculation.

 

[Ron Weiner]

OOOOpppsss... Gotta' learn how to read. Sorry. :-( You can use the
following Function as the RecordSource of your text box

Public Function Ave3Fields(fld1 As Variant, fld2 As Variant, fld3 As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = Nz(fld1, 0) + Nz(fld2, 0) + Nz(fld3, 0)
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Ron W

 

[Guest]

Hi, Frank.

I don't think there's a built-in approach to this, but you can create a
function to do what you want. The following isn't particularly elegant, but
it works. Set the textbox Control Source to:

= MyAverage([Field1],[Field2],[Field3])

Enter the following code in a public module (on the Module tab):

Public Function MyAverage(sglParam1, sglParam2, sglParam3) As Single
Dim intDivisor As Integer

; Divisor if all textboxes have a value
intDivisor = 3

; If any parameter is null, subtract one from divisor
If Nz(sglParam1) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglParam2) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglParam3) = 0 Then
intDivisor = intDivisor - 1
End If

; Return the average, or zero if all are blank
If intDivisor <> 0 Then
MyAverage = (Nz(sglParam1) + Nz(sglParam2) + Nz(sglParam3)) /
intDivisor
Else
MyAverage = 0
End If
End Function

 

[Guest]

Ron,

Thanks. I've incorporated everything you wrote by replacing fld# with my
own field names, plus Ave3Fields with my own average field name (see below).
However, I think I'm placing the code in the incorrect place.

When I right click on the properties window, do I add this code as an
"event"?? Because when I right click the appropriate field, the data tab
doesn't bring up the code builder, it only brings up the Expression builder.
I only get the Code builder under the event tab.

I added your code under the "Before Update" event. It now reads like this...

Private Sub Average_Interest_Rate_BeforeUpdate(Cancel As Integer)
Public Function AverageInterestRate(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, fld3 As Variant, ThirdMortgageInterest As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = Nz(FirstMortgageInterest, 0) + Nz(SecondMortgageInterest, 0)
+ Nz(ThirdMortgageInterest, 0)
If intCount = 0 Then
AverageInterestRate = Null
Else
AverageInterestRate = varTotal / intCount
End If
End Function
End Sub

What's wrong? Sorry for my ignorance. I'm just learning VB programming
this month.

Frank

 

[Ron Weiner]

Sheesh I am just full of bad advice today. Truly sorry!!!! The function as
I had written it will not work properly. Here is a replacement that I have
actually checked for correct operation.

Public Function Ave3Fields(fld1 As Variant, fld2 _
As Variant, fld3 As Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = CDbl(Nz(fld1, 0)) + CDbl(Nz(fld2, 0)) _
+ CDbl(Nz(fld3, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Just paste the above function in the code module of your form OUTSIDE of any
other function or sub. Watch out for any lines that the newsreader wraps.
After pasting there should be no red text. Or if you prefer you can go
ahead and put it in a global module. Makes no difference.

Then in the Property Sheet of the Text Box that is showing the average, set
Control Source to =Ave3Fields([text1],[text2],[text3]) Where you replace the
text1 text2 text3 with the names of the text boxes on your form. Leave the
square brackets in place!

Access can get confused when the name of the object (text box in this case)
is the same name as the underlying field it is bound to. So if your
underlying field in the database is named FirstMortgageInterest then change
the name of the text box to something like txtFirstMortgageInterest. If I
understand your form and table structure correctly, then I believe that the
Control Source for the Text box that will display the average will be
=Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest]). However, at the rate and velocity I have been giving
you advise today I am taking nothing for granted.

Sorry for all of the confusion.

Don't hesitate to get back to me if this also crashes and burns too. Send
me the names of the textboxes, the field names they are bound to, and the
error message.

Ron W

 

[Guest]

Ron,

I must be screwing things up here. Here's what I've done. I created a new
module. I titled it AvgInterestRate.

I copied the following text in there:

Option Compare Database
Public Function Ave3Fields(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, ThirdMortgageInterest As Variant) As
Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = CDbl(Nz(FirstMortgageInterest, 0)) +
CDbl(Nz(SecondMortgageInterest, 0)) _
+ CDbl(Nz(ThirdMortgageInterest, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function


Ron, there is no red text in the module. On my table, I used the same
average field as you provided, Ave3Fields. I stored it as 'Text' and it
didn't work. I then stored it as 'Number' and that didn't work. The control
source for that field now reads,
"Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest])". And the name reads Ave3Fields.

Btw, the control source for the 3 other fields are FirstMortgageInterest,
SecondMortgageInterest, and ThirdMortgageInterest]). I added the 'txt' to
the front of each data label as you suggested.

My Ave3Field now reads, #Name?

What am I doing wrong? Btw, thanks for all your help on this.

Frank

Sheesh I am just full of bad advice today. Truly sorry!!!! The function as
I had written it will not work properly. Here is a replacement that I have
actually checked for correct operation.

Public Function Ave3Fields(fld1 As Variant, fld2 _
As Variant, fld3 As Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = CDbl(Nz(fld1, 0)) + CDbl(Nz(fld2, 0)) _
+ CDbl(Nz(fld3, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Just paste the above function in the code module of your form OUTSIDE of any
other function or sub. Watch out for any lines that the newsreader wraps.
After pasting there should be no red text. Or if you prefer you can go
ahead and put it in a global module. Makes no difference.

Then in the Property Sheet of the Text Box that is showing the average, set
Control Source to =Ave3Fields([text1],[text2],[text3]) Where you replace the
text1 text2 text3 with the names of the text boxes on your form. Leave the
square brackets in place!

Access can get confused when the name of the object (text box in this case)
is the same name as the underlying field it is bound to. So if your
underlying field in the database is named FirstMortgageInterest then change
the name of the text box to something like txtFirstMortgageInterest. If I
understand your form and table structure correctly, then I believe that the
Control Source for the Text box that will display the average will be
=Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest]). However, at the rate and velocity I have been giving
you advise today I am taking nothing for granted.

Sorry for all of the confusion.

Don't hesitate to get back to me if this also crashes and burns too. Send
me the names of the textboxes, the field names they are bound to, and the
error message.

Ron W

Ron,

Thanks. I've incorporated everything you wrote by replacing fld# with my
own field names, plus Ave3Fields with my own average field name (see below).
However, I think I'm placing the code in the incorrect place.

When I right click on the properties window, do I add this code as an
"event"?? Because when I right click the appropriate field, the data tab
doesn't bring up the code builder, it only brings up the Expression builder.
I only get the Code builder under the event tab.

I added your code under the "Before Update" event. It now reads like this...

Private Sub Average_Interest_Rate_BeforeUpdate(Cancel As Integer)
Public Function AverageInterestRate(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, fld3 As Variant, ThirdMortgageInterest As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = Nz(FirstMortgageInterest, 0) + Nz(SecondMortgageInterest, 0)
+ Nz(ThirdMortgageInterest, 0)
If intCount = 0 Then
AverageInterestRate = Null
Else
AverageInterestRate = varTotal / intCount
End If
End Function
End Sub

What's wrong? Sorry for my ignorance. I'm just learning VB programming
this month.

Frank

 

[Guest]

Ron,

To simplify things, I got rid of "percentages" in case that created the
problem. So now all 3 input fields are of Format "General Number" and Field
Size 'Double'. I also double checked the design of the forms themselves, and
they match.

The Ave3Fields is also "General Number" and 'Double'. I figured if we could
get rid of percentages, that might help the cause.

Frank

Ron,

I must be screwing things up here. Here's what I've done. I created a new
module. I titled it AvgInterestRate.

I copied the following text in there:

Option Compare Database
Public Function Ave3Fields(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, ThirdMortgageInterest As Variant) As
Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = CDbl(Nz(FirstMortgageInterest, 0)) +
CDbl(Nz(SecondMortgageInterest, 0)) _
+ CDbl(Nz(ThirdMortgageInterest, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function


Ron, there is no red text in the module. On my table, I used the same
average field as you provided, Ave3Fields. I stored it as 'Text' and it
didn't work. I then stored it as 'Number' and that didn't work. The control
source for that field now reads,
"Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest])". And the name reads Ave3Fields.

Btw, the control source for the 3 other fields are FirstMortgageInterest,
SecondMortgageInterest, and ThirdMortgageInterest]). I added the 'txt' to
the front of each data label as you suggested.

My Ave3Field now reads, #Name?

What am I doing wrong? Btw, thanks for all your help on this.

Frank

Sheesh I am just full of bad advice today. Truly sorry!!!! The function as
I had written it will not work properly. Here is a replacement that I have
actually checked for correct operation.

Public Function Ave3Fields(fld1 As Variant, fld2 _
As Variant, fld3 As Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = CDbl(Nz(fld1, 0)) + CDbl(Nz(fld2, 0)) _
+ CDbl(Nz(fld3, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Just paste the above function in the code module of your form OUTSIDE of any
other function or sub. Watch out for any lines that the newsreader wraps.
After pasting there should be no red text. Or if you prefer you can go
ahead and put it in a global module. Makes no difference.

Then in the Property Sheet of the Text Box that is showing the average, set
Control Source to =Ave3Fields([text1],[text2],[text3]) Where you replace the
text1 text2 text3 with the names of the text boxes on your form. Leave the
square brackets in place!

Access can get confused when the name of the object (text box in this case)
is the same name as the underlying field it is bound to. So if your
underlying field in the database is named FirstMortgageInterest then change
the name of the text box to something like txtFirstMortgageInterest. If I
understand your form and table structure correctly, then I believe that the
Control Source for the Text box that will display the average will be
=Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest]). However, at the rate and velocity I have been giving
you advise today I am taking nothing for granted.

Sorry for all of the confusion.

Don't hesitate to get back to me if this also crashes and burns too. Send
me the names of the textboxes, the field names they are bound to, and the
error message.

Ron W

Ron,

Thanks. I've incorporated everything you wrote by replacing fld# with my
own field names, plus Ave3Fields with my own average field name (see below).
However, I think I'm placing the code in the incorrect place.

When I right click on the properties window, do I add this code as an
"event"?? Because when I right click the appropriate field, the data tab
doesn't bring up the code builder, it only brings up the Expression builder.
I only get the Code builder under the event tab.

I added your code under the "Before Update" event. It now reads like this...

Private Sub Average_Interest_Rate_BeforeUpdate(Cancel As Integer)
Public Function AverageInterestRate(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, fld3 As Variant, ThirdMortgageInterest As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = Nz(FirstMortgageInterest, 0) + Nz(SecondMortgageInterest, 0)
+ Nz(ThirdMortgageInterest, 0)
If intCount = 0 Then
AverageInterestRate = Null
Else
AverageInterestRate = varTotal / intCount
End If
End Function
End Sub

What's wrong? Sorry for my ignorance. I'm just learning VB programming
this month.

Frank

:

OOOOpppsss... Gotta' learn how to read. Sorry. :-( You can use the
following Function as the RecordSource of your text box

Public Function Ave3Fields(fld1 As Variant, fld2 As Variant, fld3 As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = Nz(fld1, 0) + Nz(fld2, 0) + Nz(fld3, 0)
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Ron W
Ron,

I don't want to grab the average of all rows of one field. I want to grab
the average of 3 fields, on the same record. I have one record with 4
different fields. The first 3 fields represent actual input of values.
The
4th field represents the average of the first three fields (no data entry
allowed). I want the 4th field to contain the average of the 3 fields,
and
to not include blank entry fields in the average calculation. (hence my
example below)

This would then be repeated for each record in the table. So in short, I
want to grab the average of 3 different fields for the same record, and
display that result in the 4th field. Thanks.

Frank

P.S. Let me know if there's anything special about percentages that may
hinder this calculation.

:

How about

Assuming you want to fet the average of all rows of one field in your
table
SELECT Avg(YourField) AS AvgOfYourField FROM YourTable

This will ignore Rows that are Null

Ron W
Although this may seem trivial, I've been struggling all day w/ it. I
have 3
percentage fileds on a form. I would like to gather the average of
the
percentage fields that contain actual data.

Field 1 = 6.0%
Field 2 = 7.0%
Field 3 = blank

Traditional math tells you (6% + 7% + 0%) / 3 = 4.33%. But that's not
the
case here. I only have two fields populated, since the third field is
left
blank, so the average should be (6% + 7%) / 2 = 6.5%

Question is: how can I get the average of 3 fields, where only one or
two
of the fields may contain actual data?

I tried summing all the fields and then using a count function baked
in,
but
I'm having no luck. As you know, I can't use traditional math to
divide
by
the total number of fields because not all fields may contain data.

Any advice/help is greatly appreciated. Happy holidays.

Frank

 

[Guest]

Sprinks,

I copied your code as well, and it didn't work either. Here's how it
reads....

Public Function Ave3Fields(FirstMortgageInterest, SecondMortgageInterest,
ThirdMortgageInterest) As Single
Dim intDivisor As Integer
intDivisor = 3

If Nz(sglFirstMortgageInterest) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglSecondMortgageInterest) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglThirdMortgageInterest) = 0 Then
intDivisor = intDivisor - 1
End If

If intDivisor <> 0 Then
Ave3Fields = (Nz(sglFirstMortgageInterest) +
Nz(sglSecondMortgageInterest) + Nz(sglThirdMortgageInterest)) / intDivisor
Else
Ave3Fields = 0
End If
End Function

I also tried writing the letters "txt" in front of each of the 3 input
fields since their Name Labels contain "txt" in front of them. See my
posting exchange w/ Ron.

I've been at this all day. I'm about to call it quits and go do some last
minute Christmas shopping. Any help you can give me is greatly appreciated.
Thanks so much.

Frank

Hi, Frank.

I don't think there's a built-in approach to this, but you can create a
function to do what you want. The following isn't particularly elegant, but
it works. Set the textbox Control Source to:

= MyAverage([Field1],[Field2],[Field3])

Enter the following code in a public module (on the Module tab):

Public Function MyAverage(sglParam1, sglParam2, sglParam3) As Single
Dim intDivisor As Integer

; Divisor if all textboxes have a value
intDivisor = 3

; If any parameter is null, subtract one from divisor
If Nz(sglParam1) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglParam2) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglParam3) = 0 Then
intDivisor = intDivisor - 1
End If

; Return the average, or zero if all are blank
If intDivisor <> 0 Then
MyAverage = (Nz(sglParam1) + Nz(sglParam2) + Nz(sglParam3)) /
intDivisor
Else
MyAverage = 0
End If
End Function

Ron,

I don't want to grab the average of all rows of one field. I want to grab
the average of 3 fields, on the same record. I have one record with 4
different fields. The first 3 fields represent actual input of values. The
4th field represents the average of the first three fields (no data entry
allowed). I want the 4th field to contain the average of the 3 fields, and
to not include blank entry fields in the average calculation. (hence my
example below)

This would then be repeated for each record in the table. So in short, I
want to grab the average of 3 different fields for the same record, and
display that result in the 4th field. Thanks.

Frank

P.S. Let me know if there's anything special about percentages that may
hinder this calculation.

 

[Ron Weiner]

You need to change the "Name" of the Text Box Control to
txtFirstMortgageInterest etc. Not its attached Label. In the Property
Sheet for the text box, the topmost property is Name. That is what you use
to reference the object (in this case the text box). Make sure the text in
the Name property of the three text boxes are txtFirstMortgageInterest,
txtSecondMortgageInterest, and txtThirdMortgageInterest. The text in Name
property of the 3 interest fields is what we want to put in the expression
that goes in the Control Source of the field that will display the average.
Soooo... The Control Source of the text box that displays the average will
read thusly:

=Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest])

There are No Quotes and do Not forget the Equal sign. The Control Source of
a text box can be found in the property sheet just below the Name property.

If the function is in a bas Module you can easily test to make sure the
function is working properly by trying it from the Immediate Window. Open
the Immediate window if it is not already open by typing a <Control>G from
just about anywhere in Access. In the Immediate window type the following
expression. ? Ave3Fields (.08,.07,.06) then hit enter. On the next line of
the immediate window you should print. 0.07 If you do not see anything or
something else got printed then the problem is in the function. Otherwise
the problem is in the way you are calling it from the Control Source of the
text box that displays the average.

If you are still having a problem after checking all the above copy your
form and the Bas module into a new access database and Email it to me at
(e-mail address removed) You will need to remove the "SpamNot" for
the email to get to me. I will then be able to see what you are doing and
give you Succinct instructions. I do not need to have any of your data or
other forms, reports, or macros.

Ron W

Ron,

I must be screwing things up here. Here's what I've done. I created a new
module. I titled it AvgInterestRate.

I copied the following text in there:

Option Compare Database
Public Function Ave3Fields(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, ThirdMortgageInterest As Variant) As
Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = CDbl(Nz(FirstMortgageInterest, 0)) +
CDbl(Nz(SecondMortgageInterest, 0)) _
+ CDbl(Nz(ThirdMortgageInterest, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function


Ron, there is no red text in the module. On my table, I used the same
average field as you provided, Ave3Fields. I stored it as 'Text' and it
didn't work. I then stored it as 'Number' and that didn't work. The control
source for that field now reads,
"Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest])". And the name reads Ave3Fields.

Btw, the control source for the 3 other fields are FirstMortgageInterest,
SecondMortgageInterest, and ThirdMortgageInterest]). I added the 'txt' to
the front of each data label as you suggested.

My Ave3Field now reads, #Name?

What am I doing wrong? Btw, thanks for all your help on this.

Frank

Sheesh I am just full of bad advice today. Truly sorry!!!! The function as
I had written it will not work properly. Here is a replacement that I have
actually checked for correct operation.

Public Function Ave3Fields(fld1 As Variant, fld2 _
As Variant, fld3 As Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = CDbl(Nz(fld1, 0)) + CDbl(Nz(fld2, 0)) _
+ CDbl(Nz(fld3, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Just paste the above function in the code module of your form OUTSIDE of any
other function or sub. Watch out for any lines that the newsreader wraps.
After pasting there should be no red text. Or if you prefer you can go
ahead and put it in a global module. Makes no difference.

Then in the Property Sheet of the Text Box that is showing the average, set
Control Source to =Ave3Fields([text1],[text2],[text3]) Where you replace the
text1 text2 text3 with the names of the text boxes on your form. Leave the
square brackets in place!

Access can get confused when the name of the object (text box in this case)
is the same name as the underlying field it is bound to. So if your
underlying field in the database is named FirstMortgageInterest then change
the name of the text box to something like txtFirstMortgageInterest. If I
understand your form and table structure correctly, then I believe that the
Control Source for the Text box that will display the average will be
=Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest]). However, at the rate and velocity I have been giving
you advise today I am taking nothing for granted.

Sorry for all of the confusion.

Don't hesitate to get back to me if this also crashes and burns too. Send
me the names of the textboxes, the field names they are bound to, and the
error message.

Ron W

Ron,

Thanks. I've incorporated everything you wrote by replacing fld# with my
own field names, plus Ave3Fields with my own average field name (see below).
However, I think I'm placing the code in the incorrect place.

When I right click on the properties window, do I add this code as an
"event"?? Because when I right click the appropriate field, the data tab
doesn't bring up the code builder, it only brings up the Expression builder.
I only get the Code builder under the event tab.

I added your code under the "Before Update" event. It now reads like this...

Private Sub Average_Interest_Rate_BeforeUpdate(Cancel As Integer)
Public Function AverageInterestRate(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, fld3 As Variant,

ThirdMortgageInterest
As

Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = Nz(FirstMortgageInterest, 0) +

Nz(SecondMortgageInterest,
0)

+ Nz(ThirdMortgageInterest, 0)
If intCount = 0 Then
AverageInterestRate = Null
Else
AverageInterestRate = varTotal / intCount
End If
End Function
End Sub

What's wrong? Sorry for my ignorance. I'm just learning VB programming
this month.

Frank

:

OOOOpppsss... Gotta' learn how to read. Sorry. :-( You can use the
following Function as the RecordSource of your text box

Public Function Ave3Fields(fld1 As Variant, fld2 As Variant, fld3 As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = Nz(fld1, 0) + Nz(fld2, 0) + Nz(fld3, 0)
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Ron W
Ron,

I don't want to grab the average of all rows of one field. I want

to
grab

the average of 3 fields, on the same record. I have one record with 4
different fields. The first 3 fields represent actual input of values.
The
4th field represents the average of the first three fields (no

data
entry

allowed). I want the 4th field to contain the average of the 3 fields,
and
to not include blank entry fields in the average calculation.

(hence
my

example below)

This would then be repeated for each record in the table. So in short, I
want to grab the average of 3 different fields for the same

record,
and

display that result in the 4th field. Thanks.

Frank

P.S. Let me know if there's anything special about percentages

that
may

hinder this calculation.

:

How about

Assuming you want to fet the average of all rows of one field in your
table
SELECT Avg(YourField) AS AvgOfYourField FROM YourTable

This will ignore Rows that are Null

Ron W
Although this may seem trivial, I've been struggling all day

w/
it. I

have 3
percentage fileds on a form. I would like to gather the

average
of

the
percentage fields that contain actual data.

Field 1 = 6.0%
Field 2 = 7.0%
Field 3 = blank

Traditional math tells you (6% + 7% + 0%) / 3 = 4.33%. But

that's
not

the
case here. I only have two fields populated, since the third field is
left
blank, so the average should be (6% + 7%) / 2 = 6.5%

Question is: how can I get the average of 3 fields, where

only
one or

two
of the fields may contain actual data?

I tried summing all the fields and then using a count function baked
in,
but
I'm having no luck. As you know, I can't use traditional math to
divide
by
the total number of fields because not all fields may contain data.

Any advice/help is greatly appreciated. Happy holidays.

Frank

 

[Guest]

Ron,

I had the labels below correctly as you stated. So I ran the immediate
window as you suggested and got 0.07. So I figured it had to be something in
the way it was being called. After going through every possible permutation,
I FINALLY GOT IT!!

It was pretty much a syntax error on my end. Ron, you've been great. Happy
holidays to you and your family.

All the best,

Frank

You need to change the "Name" of the Text Box Control to
txtFirstMortgageInterest etc. Not its attached Label. In the Property
Sheet for the text box, the topmost property is Name. That is what you use
to reference the object (in this case the text box). Make sure the text in
the Name property of the three text boxes are txtFirstMortgageInterest,
txtSecondMortgageInterest, and txtThirdMortgageInterest. The text in Name
property of the 3 interest fields is what we want to put in the expression
that goes in the Control Source of the field that will display the average.
Soooo... The Control Source of the text box that displays the average will
read thusly:

=Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest])

There are No Quotes and do Not forget the Equal sign. The Control Source of
a text box can be found in the property sheet just below the Name property.

If the function is in a bas Module you can easily test to make sure the
function is working properly by trying it from the Immediate Window. Open
the Immediate window if it is not already open by typing a <Control>G from
just about anywhere in Access. In the Immediate window type the following
expression. ? Ave3Fields (.08,.07,.06) then hit enter. On the next line of
the immediate window you should print. 0.07 If you do not see anything or
something else got printed then the problem is in the function. Otherwise
the problem is in the way you are calling it from the Control Source of the
text box that displays the average.

If you are still having a problem after checking all the above copy your
form and the Bas module into a new access database and Email it to me at
(e-mail address removed) You will need to remove the "SpamNot" for
the email to get to me. I will then be able to see what you are doing and
give you Succinct instructions. I do not need to have any of your data or
other forms, reports, or macros.

Ron W

Ron,

I must be screwing things up here. Here's what I've done. I created a new
module. I titled it AvgInterestRate.

I copied the following text in there:

Option Compare Database
Public Function Ave3Fields(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, ThirdMortgageInterest As Variant) As
Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = CDbl(Nz(FirstMortgageInterest, 0)) +
CDbl(Nz(SecondMortgageInterest, 0)) _
+ CDbl(Nz(ThirdMortgageInterest, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function


Ron, there is no red text in the module. On my table, I used the same
average field as you provided, Ave3Fields. I stored it as 'Text' and it
didn't work. I then stored it as 'Number' and that didn't work. The control
source for that field now reads,
"Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest])". And the name reads Ave3Fields.

Btw, the control source for the 3 other fields are FirstMortgageInterest,
SecondMortgageInterest, and ThirdMortgageInterest]). I added the 'txt' to
the front of each data label as you suggested.

My Ave3Field now reads, #Name?

What am I doing wrong? Btw, thanks for all your help on this.

Frank

Sheesh I am just full of bad advice today. Truly sorry!!!! The function as
I had written it will not work properly. Here is a replacement that I have
actually checked for correct operation.

Public Function Ave3Fields(fld1 As Variant, fld2 _
As Variant, fld3 As Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = CDbl(Nz(fld1, 0)) + CDbl(Nz(fld2, 0)) _
+ CDbl(Nz(fld3, 0))
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Just paste the above function in the code module of your form OUTSIDE of any
other function or sub. Watch out for any lines that the newsreader wraps.
After pasting there should be no red text. Or if you prefer you can go
ahead and put it in a global module. Makes no difference.

Then in the Property Sheet of the Text Box that is showing the average, set
Control Source to =Ave3Fields([text1],[text2],[text3]) Where you replace the
text1 text2 text3 with the names of the text boxes on your form. Leave the
square brackets in place!

Access can get confused when the name of the object (text box in this case)
is the same name as the underlying field it is bound to. So if your
underlying field in the database is named FirstMortgageInterest then change
the name of the text box to something like txtFirstMortgageInterest. If I
understand your form and table structure correctly, then I believe that the
Control Source for the Text box that will display the average will be
=Ave3Fields([txtFirstMortgageInterest],[txtSecondMortgageInterest],[txtThird
MortgageInterest]). However, at the rate and velocity I have been giving
you advise today I am taking nothing for granted.

Sorry for all of the confusion.

Don't hesitate to get back to me if this also crashes and burns too. Send
me the names of the textboxes, the field names they are bound to, and the
error message.

Ron W

Ron,

Thanks. I've incorporated everything you wrote by replacing fld# with my
own field names, plus Ave3Fields with my own average field name (see
below).
However, I think I'm placing the code in the incorrect place.

When I right click on the properties window, do I add this code as an
"event"?? Because when I right click the appropriate field, the data tab
doesn't bring up the code builder, it only brings up the Expression
builder.
I only get the Code builder under the event tab.

I added your code under the "Before Update" event. It now reads like
this...

Private Sub Average_Interest_Rate_BeforeUpdate(Cancel As Integer)
Public Function AverageInterestRate(FirstMortgageInterest As Variant,
SecondMortgageInterest As Variant, fld3 As Variant, ThirdMortgageInterest
As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(FirstMortgageInterest) Then intCount = intCount - 1
If IsNull(SecondMortgageInterest) Then intCount = intCount - 1
If IsNull(ThirdMortgageInterest) Then intCount = intCount - 1
varTotal = Nz(FirstMortgageInterest, 0) + Nz(SecondMortgageInterest,
0)
+ Nz(ThirdMortgageInterest, 0)
If intCount = 0 Then
AverageInterestRate = Null
Else
AverageInterestRate = varTotal / intCount
End If
End Function
End Sub

What's wrong? Sorry for my ignorance. I'm just learning VB programming
this month.

Frank

:

OOOOpppsss... Gotta' learn how to read. Sorry. :-( You can use the
following Function as the RecordSource of your text box

Public Function Ave3Fields(fld1 As Variant, fld2 As Variant, fld3 As
Variant) As Variant
Dim varTotal As Variant, intCount As Integer

intCount = 3
If IsNull(fld1) Then intCount = intCount - 1
If IsNull(fld2) Then intCount = intCount - 1
If IsNull(fld3) Then intCount = intCount - 1
varTotal = Nz(fld1, 0) + Nz(fld2, 0) + Nz(fld3, 0)
If intCount = 0 Then
Ave3Fields = Null
Else
Ave3Fields = varTotal / intCount
End If
End Function

Ron W
Ron,

I don't want to grab the average of all rows of one field. I want to
grab
the average of 3 fields, on the same record. I have one record with 4
different fields. The first 3 fields represent actual input of
values.
The
4th field represents the average of the first three fields (no data
entry
allowed). I want the 4th field to contain the average of the 3
fields,
and
to not include blank entry fields in the average calculation. (hence
my
example below)

This would then be repeated for each record in the table. So in
short, I
want to grab the average of 3 different fields for the same record,
and
display that result in the 4th field. Thanks.

Frank

P.S. Let me know if there's anything special about percentages that
may
hinder this calculation.

:

How about

Assuming you want to fet the average of all rows of one field in
your
table
SELECT Avg(YourField) AS AvgOfYourField FROM YourTable

This will ignore Rows that are Null

Ron W
Although this may seem trivial, I've been struggling all day w/
it. I
have 3
percentage fileds on a form. I would like to gather the average
of
the
percentage fields that contain actual data.

Field 1 = 6.0%
Field 2 = 7.0%
Field 3 = blank

Traditional math tells you (6% + 7% + 0%) / 3 = 4.33%. But that's
not
the
case here. I only have two fields populated, since the third
field is
left
blank, so the average should be (6% + 7%) / 2 = 6.5%

Question is: how can I get the average of 3 fields, where only
one or
two
of the fields may contain actual data?

I tried summing all the fields and then using a count function
baked
in,
but
I'm having no luck. As you know, I can't use traditional math to
divide
by
the total number of fields because not all fields may contain
data.

Any advice/help is greatly appreciated. Happy holidays.

Frank

 

[Guest]

Hi, Frank.

You named the function's parameters FirstMortgageInterest,...etc., but the
If statements below have different names: sglFirstMortageInterest, ....etc.
Call them by the same thing, and the code should work, as it did for me when
I tested it.

Hope that helps. Happy holidays.

Sprinks

Sprinks,

I copied your code as well, and it didn't work either. Here's how it
reads....

Public Function Ave3Fields(FirstMortgageInterest, SecondMortgageInterest,
ThirdMortgageInterest) As Single
Dim intDivisor As Integer
intDivisor = 3

If Nz(sglFirstMortgageInterest) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglSecondMortgageInterest) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglThirdMortgageInterest) = 0 Then
intDivisor = intDivisor - 1
End If

If intDivisor <> 0 Then
Ave3Fields = (Nz(sglFirstMortgageInterest) +
Nz(sglSecondMortgageInterest) + Nz(sglThirdMortgageInterest)) / intDivisor
Else
Ave3Fields = 0
End If
End Function

I also tried writing the letters "txt" in front of each of the 3 input
fields since their Name Labels contain "txt" in front of them. See my
posting exchange w/ Ron.

I've been at this all day. I'm about to call it quits and go do some last
minute Christmas shopping. Any help you can give me is greatly appreciated.
Thanks so much.

Frank

Hi, Frank.

I don't think there's a built-in approach to this, but you can create a
function to do what you want. The following isn't particularly elegant, but
it works. Set the textbox Control Source to:

= MyAverage([Field1],[Field2],[Field3])

Enter the following code in a public module (on the Module tab):

Public Function MyAverage(sglParam1, sglParam2, sglParam3) As Single
Dim intDivisor As Integer

; Divisor if all textboxes have a value
intDivisor = 3

; If any parameter is null, subtract one from divisor
If Nz(sglParam1) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglParam2) = 0 Then
intDivisor = intDivisor - 1
End If

If Nz(sglParam3) = 0 Then
intDivisor = intDivisor - 1
End If

; Return the average, or zero if all are blank
If intDivisor <> 0 Then
MyAverage = (Nz(sglParam1) + Nz(sglParam2) + Nz(sglParam3)) /
intDivisor
Else
MyAverage = 0
End If
End Function

Ron,

I don't want to grab the average of all rows of one field. I want to grab
the average of 3 fields, on the same record. I have one record with 4
different fields. The first 3 fields represent actual input of values. The
4th field represents the average of the first three fields (no data entry
allowed). I want the 4th field to contain the average of the 3 fields, and
to not include blank entry fields in the average calculation. (hence my
example below)

This would then be repeated for each record in the table. So in short, I
want to grab the average of 3 different fields for the same record, and
display that result in the 4th field. Thanks.

Frank

P.S. Let me know if there's anything special about percentages that may
hinder this calculation.

:

How about

Assuming you want to fet the average of all rows of one field in your table
SELECT Avg(YourField) AS AvgOfYourField FROM YourTable

This will ignore Rows that are Null

Ron W
Although this may seem trivial, I've been struggling all day w/ it. I
have 3
percentage fileds on a form. I would like to gather the average of the
percentage fields that contain actual data.

Field 1 = 6.0%
Field 2 = 7.0%
Field 3 = blank

Traditional math tells you (6% + 7% + 0%) / 3 = 4.33%. But that's not the
case here. I only have two fields populated, since the third field is
left
blank, so the average should be (6% + 7%) / 2 = 6.5%

Question is: how can I get the average of 3 fields, where only one or two
of the fields may contain actual data?

I tried summing all the fields and then using a count function baked in,
but
I'm having no luck. As you know, I can't use traditional math to divide
by
the total number of fields because not all fields may contain data.

Any advice/help is greatly appreciated. Happy holidays.

Frank