Average within a record

[Guest]

Our database has 3 fields that track amounts within each record. Then we have
a fourth field that we put the average of the 3 amount fields in. We have to
calculate the average by hand on a calculator, then input it. Is there a way
we can have access calculate the average within a record? We use a form for
the record.

 

[Al Camp]

Darla,
There's no need to save the average in a "bound" field. Since you have
the 3 amounts stored in bound fields, you can just use an unbound calculated
field to calculate the average "on the fly" and display that result.
An unbound field with a ControlSource of...
= ([Amt1]+[Amt2]+[Amt3]) / 3
will always show the correct average... even if you change any of the Amts.
Use this same method in any subsequent query, report, or form.
hth
Al camp

 

[Guest]

Al is correct. What he did not say, explicitly, is never carry a calculated
value in a table. First, it takes up extra space, and second it can get out
of sync. One good practice is if you have more than one place in your
application that uses a calculated amount, create a function that returns the
calculation. Less coding and it will always be the same.

 

[Guest]

Within Access where would I put such a function? Do I use the "Build Event"
when iI right click the avgField?
Or should I go into "properties" of the avgField and paste the code into the
expression builder?
I'm kinda new to programming in Access <lol>
Although I have knowledge of C and C##, some visual basic.

 

[Guest]

Also, sometimes there is a null value in one or more of the
fields were are trying to average so dividing by 3 isn't always a constant.

 

[Guest]

You could put this as a function in the form module. I would suggest,
however, you put it in a standard module so it can be called from any place
in the application. Now, I have a problem with your statement that dividing
be 3 is not always correct. Logically, it would be. A Null indicates no
value. Technically, it is different that 0, but from a business perspective
it seems it would be. So, if one field is Null do you divide by 2, and if
two are Null you just use the 1? What if all 3 are null? What about zeros?
Here is the basics, assuming you will always divide by 3 and it handles Nulls
as zeros:

Call it:

Me.txtAvg = CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/3
End Function

Just in case you need to exclude Nulls:

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

My guess it that you would need it in 4 places in your form. One in the On
Current event of the Form, and in the After Update event for each of the 3
amount fields.

 

[Al Camp]

Darla,
Since this is just a simple calculation, there should be no need for any
"coding".
Just place a text control on your form, and place this in the
ControlSource of that text control.
(use your own field names)
= (Nz([Amt1]) + Nz([Amt2]) + Nz([Amt3])) / (Abs(Not IsNull([Amt1])) +
Abs(Not IsNull([Amt2])) + Abs(Not IsNull([Amt3])))

This will always "correctly" display the average of all 3 numbers...
looks difficult, but bear with me.

This part adds the 3 numbers regardless if there are nulls or not
(Nz([Amt1])+Nz([Amt2])+Nz([Amt3]))

This part determines how which amounts are null, and does not include
that in the average "divisor" value. Ex. If you only have 2 amounts
entered, the average divisor should be 2, not 3. The same for 1 or 3
amounts...
(Abs(Not IsNull([Amt1]))+Abs(Not IsNull([Amt2]))+Abs(Not IsNull([Amt3])))

hth
Al Camp

 

[Guest]

Al,
I like your solution. I do, however have a question and two comment.
First, the comments.
1. If all 3 values are Null, you will get Runtime Error 6 - Overflow. (I
tested it)
2. It would be a good idea to complete the Nz function. Although Nz([Amt1])
will evaluate to 0, for clarity and to return a zero value, I would recommend
Nz([Amt1],0).

If this is the control source for the control, when will it update?

Darla,
Since this is just a simple calculation, there should be no need for any
"coding".
Just place a text control on your form, and place this in the
ControlSource of that text control.
(use your own field names)
= (Nz([Amt1]) + Nz([Amt2]) + Nz([Amt3])) / (Abs(Not IsNull([Amt1])) +
Abs(Not IsNull([Amt2])) + Abs(Not IsNull([Amt3])))

This will always "correctly" display the average of all 3 numbers...
looks difficult, but bear with me.

This part adds the 3 numbers regardless if there are nulls or not
(Nz([Amt1])+Nz([Amt2])+Nz([Amt3]))

This part determines how which amounts are null, and does not include
that in the average "divisor" value. Ex. If you only have 2 amounts
entered, the average divisor should be 2, not 3. The same for 1 or 3
amounts...
(Abs(Not IsNull([Amt1]))+Abs(Not IsNull([Amt2]))+Abs(Not IsNull([Amt3])))

hth
Al Camp

Also, sometimes there is a null value in one or more of the
fields were are trying to average so dividing by 3 isn't always a
constant.

 

[Guest]

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

 

[Guest]

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

No problem. Just add a column to your query. In the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

It says undefined function 'CalcAvg' in expression
--
Darlahood

No problem. Just add a column to your query. In the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

:

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

Did you create a function named CalcAvg based on the example I posted? Where
did you put it? It needs to be in a standard module and it needs to be
identified as
Public Sub

It says undefined function 'CalcAvg' in expression
--
Darlahood

No problem. Just add a column to your query. In the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood


:

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

:

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

No I didn't create the function. I just pasted the example into the field row
of my query.
Should I create a module? If so what would the code be.
I understand some programming, but I dont have any books on programming for
access.
Are there any examples in the Northwind DataBase?
I think I may need a good book on programming/tweaking access.
Do you have any suggestions?
--
Darlahood

Did you create a function named CalcAvg based on the example I posted? Where
did you put it? It needs to be in a standard module and it needs to be
identified as
Public Sub

It says undefined function 'CalcAvg' in expression
--
Darlahood

No problem. Just add a column to your query. In the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)



:

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood


:

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

:

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

1. Create a module. I would name it something like basMiscUtilities
Paste this function in the module and save it.

Puiblic Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
Else
CalcAvg = 0
End If
End Function

2. Add a column to your query and paste this in the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)
Where it says Me.txtAmt1, replace it with the name of the textbox on your
form, same for the other two

As to good reference material, I don't personally care much for what
Microsoft publishes. What I use is Access 2002 Desktop Developer's Handbook
by Paul Litwin, Ken Getz, and Mike Gunderloy, published by Sybex. I have
heard they are not planning a 20003 version release, which is disappointing,
but I don't know that to be a fact.

My suggestion would be to go to a bookstore that is heavy in techincal
manuals and thumb through what is available for Access and buy the one that
makes most sense to you.

Good Luck.

No I didn't create the function. I just pasted the example into the field row
of my query.
Should I create a module? If so what would the code be.
I understand some programming, but I dont have any books on programming for
access.
Are there any examples in the Northwind DataBase?
I think I may need a good book on programming/tweaking access.
Do you have any suggestions?
--
Darlahood

Did you create a function named CalcAvg based on the example I posted? Where
did you put it? It needs to be in a standard module and it needs to be
identified as
Public Sub

It says undefined function 'CalcAvg' in expression
--
Darlahood


:

No problem. Just add a column to your query. In the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)



:

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood


:

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

:

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

Thanks so much for the help with this. My boss pasted the original formula
into the report and was able to fix our problem. I did implement the function
and it pops up a window and asks me for values?? Is it possible to use this
as an interface for a query to input specific data into a report ie we have a
field on a report that needs a different code each day. Could the pop up
function allow us to enter this info? I'm going to do some testing and also
I'll pick up a book that looks appropriate. Thx again for all your help. We
could not have fixed this average problem without you!!
--
Darlahood

1. Create a module. I would name it something like basMiscUtilities
Paste this function in the module and save it.

Puiblic Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
Else
CalcAvg = 0
End If
End Function

2. Add a column to your query and paste this in the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)
Where it says Me.txtAmt1, replace it with the name of the textbox on your
form, same for the other two

As to good reference material, I don't personally care much for what
Microsoft publishes. What I use is Access 2002 Desktop Developer's Handbook
by Paul Litwin, Ken Getz, and Mike Gunderloy, published by Sybex. I have
heard they are not planning a 20003 version release, which is disappointing,
but I don't know that to be a fact.

My suggestion would be to go to a bookstore that is heavy in techincal
manuals and thumb through what is available for Access and buy the one that
makes most sense to you.

Good Luck.

No I didn't create the function. I just pasted the example into the field row
of my query.
Should I create a module? If so what would the code be.
I understand some programming, but I dont have any books on programming for
access.
Are there any examples in the Northwind DataBase?
I think I may need a good book on programming/tweaking access.
Do you have any suggestions?
--
Darlahood

Did you create a function named CalcAvg based on the example I posted? Where
did you put it? It needs to be in a standard module and it needs to be
identified as
Public Sub

:

It says undefined function 'CalcAvg' in expression
--
Darlahood


:

No problem. Just add a column to your query. In the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)



:

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood


:

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

:

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!

 

[Guest]

Thanks so much for your kind comments.

You could use it for your report query. Exactly where to put it, I don't
know, because I don't know where your report is opened and how the
information you are entering is used in the query.

Thanks so much for the help with this. My boss pasted the original formula
into the report and was able to fix our problem. I did implement the function
and it pops up a window and asks me for values?? Is it possible to use this
as an interface for a query to input specific data into a report ie we have a
field on a report that needs a different code each day. Could the pop up
function allow us to enter this info? I'm going to do some testing and also
I'll pick up a book that looks appropriate. Thx again for all your help. We
could not have fixed this average problem without you!!
--
Darlahood

1. Create a module. I would name it something like basMiscUtilities
Paste this function in the module and save it.

Puiblic Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
Else
CalcAvg = 0
End If
End Function

2. Add a column to your query and paste this in the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)
Where it says Me.txtAmt1, replace it with the name of the textbox on your
form, same for the other two

As to good reference material, I don't personally care much for what
Microsoft publishes. What I use is Access 2002 Desktop Developer's Handbook
by Paul Litwin, Ken Getz, and Mike Gunderloy, published by Sybex. I have
heard they are not planning a 20003 version release, which is disappointing,
but I don't know that to be a fact.

My suggestion would be to go to a bookstore that is heavy in techincal
manuals and thumb through what is available for Access and buy the one that
makes most sense to you.

Good Luck.

No I didn't create the function. I just pasted the example into the field row
of my query.
Should I create a module? If so what would the code be.
I understand some programming, but I dont have any books on programming for
access.
Are there any examples in the Northwind DataBase?
I think I may need a good book on programming/tweaking access.
Do you have any suggestions?
--
Darlahood


:

Did you create a function named CalcAvg based on the example I posted? Where
did you put it? It needs to be in a standard module and it needs to be
identified as
Public Sub

:

It says undefined function 'CalcAvg' in expression
--
Darlahood


:

No problem. Just add a column to your query. In the Field row:
AmtAvg: CalcAvg(Me.txtAmt1, Me.txtAmt2, Me.txtAmt3)



:

Oh Goodness!~
I just found out that we need the avg to be used in a query, and the
avgField needs to be joined to the table...When we do a query on the data it
isnt "bound" to the table and we dont get any data...

I tried making an event for the avg in the code editor but to no avail...

Ho boy anymore help would be greatly appreciated Thank you very much!
--
Darlahood


:

#1. See my previous post. This is because you are getting a divide by 0
when all 3 fields are empty.
#2. Yes, but you will still get the problem in #1. You can use the function
I posted earlier (here it is again) in place of the formula as your control
source. It will avoid the divide by 0 problem.

Function CalcAvg(sngAmt1, sngAmt2, sngAmt3) As Single
Dim lngDivisor as Long
lngDivisor = 3
If IsNull(sngAmt1) Then lngDivisor = 2
If IsNull(sngAmt2) Then lngDivisor = 1
If IsNull(sngAmt3) Then lngDivisor = 0
If lngDivisor > 0 Then
CalcAvg = (Nz(sngAmt1,0) + Nz(sngAmt2,0) + Nz(sngAmt3,0))/lngDivisor
End If
End Function

#3. Great, I hoped that would work.

:

You people are Gods!!
Thank you so much for your help. Nobody has been able to help me with this.

now for the questions:
#1. The avgField displays #Num! when the fields are blank. When we have 0 in
the fields it displays 0.

#2. To implement the return 0, do I modify the current code of:
=(Nz([Press#1Moisture]))/(Abs(Not IsNull([Press#1Moisture])))
To Read:
=(Nz([Press#1Moisture],0))/(Abs(Not IsNull([Press#1Moisture])))

#3. It updates when I click to another field.

Thanks So Much!!