Expression for Avg that does not include Null values

[PhilOfWalton]

This is air code it may need cleaning up a bit

Function GetAverage() as Single
    Dim Total as Single
    Dim Divisor as Integer
    Dim i as Integer

    ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
    For i  = 1 to 8
        If Not IsNull(Me.Controls("LineSpeed" & Cstr(i))) then
             Divisor = Divisor + 1
        End If
    Next i

    If Divisor = 0 then          ' All blank
        GetAverage = Null
        Exit Function
    End if

    ' Now get the totals using a similar method
    For i  = 1 to 8
        If Not IsNull(Me.Controls("LineSpeed" & Cstr(i))) then
             Total = Total + Me.Controls("LineSpeed" & cstr(i))
        End If
    Next i

    GetAverage = Total / Divisor
Exit Function

Then on the Current of the form and on After Update of all 8 fields use

    Me!TotalLineSpeed = GetAverage()

Phil

[NeoPa]

Kevin.

You said you were using a calculated field in a table. If you're using VBA instead then the question's completely different.

I would just add that, unlike Phil, I strongly recommend using brackets [] around field names in SQL or expressions regardless of their being absolutely necessary. It immediately tells the reader that the contents are a reference to an object and not any part of the syntax itself. When using SQL I always surround any references in the square brackets.

[DJRhino1175]

NeoPa,

I am using a calculated field in the table itself. It would not let me use the NZ() function, so I kicked around the idea of maybe using VBA instead. If this is the better route fine as long as it puts the data in the table field.

Now I built VBA code Per what Phil suggested. Unfortunately its not working for me.

[DJRhino1175]

I put this in the After update in all 8 Linespeed fields and the TotalLineSpeed Field:

=[Me]![TotalLineSpeed]=GetAverage()

This is the code that's behind it:

Function GetAverage() As Single
    Dim Total As Single
    Dim Divisor As Integer
    Dim i As Integer
 
    ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
    For i = LineSpeed1 To LineSpeed8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Divisor = Divisor + 1
        End If
    Next i
 
    If Divisor = 0 Then          ' All blank
        GetAverage = Null
        Exit Function
    End If
 
    ' Now get the totals using a similar method
    For i = 1 To 8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Total = Total + Me.Controls("LineSpeed" & CStr(i))
        End If
    Next i
 
    GetAverage = Total / Divisor
End Function

I had to put End function as it gave me an error using Exit Function

[DJRhino1175]

Is it an issue that I'm pulling the lineSpeed data form a query?

Here is the code for how I get the data from the LineSpeed:

Private Sub DrawingNumber1_AfterUpdate()
Me!Section1 = DrawingNumber1.Column(1)
Me![LineSpeed(Meters)1] = DrawingNumber1.Column(3)
End Sub

I have the same code for each line except for the "DrawingNumber( Number) is different.

[NeoPa]

Hi Kevin.

I hear you. I might suggest that skipping so quickly from one attempt across to a completely different approach is not advisable. It may be frustrating, but perseverence until you know, because you understand, that it can't work or even that it may work but is nevertheless inadvisable, is a more sensible course of action.

You may well ask why that's important. It's important because :

1. Logic works that way. You need a firm foundation before you move on to the next step. You may skip across and find problems with the other approach. That leaves you nowhere if you haven't progressed the first to a point of solidity. No-one likes to have to start over from scratch.
2. It makes it very laborious for people to work with you. At this stage I suspect you can benefit greatly from willing assistance, and we're generally more than willing to assist.

So I guess the question becomes, now that you've put energy into both approaches, which would you prefer to push on with?

Before you answer that though, another consideration that may be preferable to both is what about not storing the data at all, but instead providing it within a query when reading the records from the table?

If the latter then we can show you (in a separate thread that you'll create for the purpose) how to write a function procedure that will take an unspecified number of Variant parameters and return the average of only those that are not Null. The query would simply return a separate value returned from this procedure with all the fields passed to it.

[DJRhino1175]

I think I like the approach we are on now, I think it will help me learn a little more VBA as I have not done a ton of VBA. I have done a lot of simple databases with simple calculations and such. I apologize if I'm frustrating anyone, I don't mean to. I only change the approached as a calculated field in the table itself seem to be very limited. I like the data going in to the table as it makes it easier to verify the data at times. I noticed now that I put the above code in my linespeeds are rounded off on the form and data table, but its correct in the data table I'm pulling it from.

[NeoPa]

OK Kevin. That's a clear answer with which we can work. I'll leave you with Phil for now but keep an eye on how things progress.

Using VBA still gives the opportunity for that clever use of procedure parameters I mentioned earlier.

[PhilOfWalton]

Right, Kevin, it looks as if it''s back to me.

Sorry about the Exit Function instead of the End Function. Well spotted, but I did warn you it was air code.

So let's get back to first principles.
What is the table layout for your data and can you provide some sample data, or better still is the database stripped of any sensitive information?

Phil

[DJRhino1175]

No problems with the Exit/End function, something I was able to figure out somewhat quickly. Database has no sensitive data or any data for that matter. I went through and removed all the calculated fields from the table and put the calculations in the description so I wouldn't need to refigure them out. I even stripped out the code you gave me, but saved it to a text doc. I attached the database as a zip file.

[PhilOfWalton]

I'm far too polite to remark on the design of your database, but when you have time, look up Normalisation and Relationships. You will have problems the way it is designed.

So to sort out your problem

At the top of every module you ever write have

Option Compare Datbase
Option Explicit

The Option Explicit will give compile errors if things aren't declared properly.

At the top of the Entry form, under your new Option Explcit, copy the following code (it's slightly different from the air code.)

Function GetAverage() As Single

    Dim Total As Single
    Dim Divisor As Integer
    Dim i As Integer
 
    ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
    For i = 1 To 8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Divisor = Divisor + 1
        End If
    Next i
 
    If Divisor = 0 Then          ' All blank
        GetAverage = Null
        Exit Function
    End If
 
    ' Now get the totals using a similar method
    For i = 1 To 8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Total = Total + Me.Controls("LineSpeed" & CStr(i))
        End If
    Next i
 
    GetAverage = Total / Divisor
    
End Function

Remove the After Update event in the TotalLineSpeed field

Add this to the OnCurrent of the Form

Private Sub Form_Current()

    TotalLineSpeed = Round(GetAverage, 2)
    
End Sub

I note that LineSpeed 1,2,3,4,5,6,7,8 are all locked, so they cannot be updated. If this is an error, you need to add the following code to all 8 fields (adjusting for the correct field name:-

Private Sub LineSpeed1_AfterUpdate()

    TotalLineSpeed = Round(GetAverage, 2)

End Sub

Phil

[DJRhino1175]

Phil,

I added the "Option Explicit" I forgot to go into the options to do this by default when I upgraded.

Also removed the code from the total field.
I have all the fields that are calculation fields(Green Label/White Textbox) locked so data doesn't get entered in those fields.

So before I start adding this code should I change how my table is? If so please explain. None of my tables truly relate that I can tell. The operators table just fields a drop down list and the Linespeeds table is feeding a query so when I select a "DrawingNum ber" it automatically inputs the Section and lineSpeed. I'm more of a beginner. I'm self taught, and for some reason have a heck of a time understanding VBA. I have been looking for classes near me and there just isn't any, the problem with living in the sticks.

Looking at the code you supplied is like looking at Klingon to me, so I'll need a little more assistance as How I need to write it as just coping and pasting it doesn't work. I don't want you to write it for me either just maybe more of an explanation as I don't know what certain things mean.

Sorry for being a huge pain, but thanks for all your help so far.

[PhilOfWalton]

Good. It seems as if we are on the same wavelength.

I am more than pleased to help, but there needs to be a lot of discussion, as I need to know what your DB if for, and how you envisage it working.
I find typing very boring & tiring. So if you want to talk about it on Skype, send me a private message, and I will give you my contact details.

Phil

[DJRhino1175]

Could do Skype, but have no Mic, but can give you the number I'm at and we can use the phone to talk it through. I feel a voice conversation would definitely be good to help with understanding.

I built this based of an Excel sheet that is being used now, but with access I can remove some of the redundancies and errors that can be typed in. I to hate typing.

[NeoPa]

Very pleased to see this progressing so well. I don't need to interfere as you're in very good hands.

However, as an Admin and friend of Phil I know things that I can't share about where you both live. Let me just say there's a fair bit of water between the two of you.

Phil. As a Skype user you can make relatively cheap calls to Kevin using Skype. Certainly cheaper than intercontinenta l telephone calls.

I hope this is some help.