Text box to show average

[fat controller]

I have been trying (unsuccessfully) to get an unbound text box on a form to show the average of four other text boxes on that form. A bit of searching led me to the code below as a possible solution, but it isn't working (clearly I am being thick again)

 Dim nbrFieldsUsed As Integer
  Dim nbrTotal As Double

  nbrFieldsUsed = 0
  nbrTotal = 0

  If Not IsNull(Me!txtEWT_STD_1) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!txtEWT_STD_1
  End If

  If Not IsNull(Me!txtEWT_STD_2) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!txtEWT_STD_2
  End If

  If Not IsNull(Me!txtEWT_STD_3) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!txtEWT_STD_3
  End If

  If Not IsNull(Me!txtEWT_STD_4) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!txtEWT_STD_4
  End If

  If nbrFieldsUsed = 0 Then
    Me!txtEWT_STD_AVE = 0
  Else
    Me!txtEWT_STD_AVE = nbrTotal / nbrFieldsUsed
  End If

My thinking was to have this code on the 'After Update' event (or maybe On Change?) of each of the four text boxes which would then give a running average in the box that shows the average?

Where am I going wrong?

 

[vbaInet]

Is it just an average that you want? Then in the Control Source:

=(Nz([COLOR="blue"]Control1[/COLOR], 0) + Nz([COLOR="blue"]Control2[/COLOR], 0) + Nz([COLOR="blue"]Control3[/COLOR], 0) + Nz([COLOR="Blue"]Control4[/COLOR], 0))/4

If it's a running average then more code will be involved.

 

[fat controller]

Is it just an average that you want? Then in the Control Source:

=(Nz([COLOR=blue]Control1[/COLOR], 0) + Nz([COLOR=blue]Control2[/COLOR], 0) + Nz([COLOR=blue]Control3[/COLOR], 0) + Nz([COLOR=Blue]Control4[/COLOR], 0))/4

If it's a running average then more code will be involved.

Ideally a running average that ignores anything that is null - on some occasions there will only be two out of the four textboxes with anything in them.

EDIT - its decimal numbers if it makes any difference?

 

[vbaInet]

We need to see sample records. And also what field is it sorted by?

 

[fat controller]

Its sorted by ID (Autonumber); Bear with me and I will knock up a quick version and upload it.

 

[vbaInet]

If it's sorted by the ID then you don't need to. Just use the DAvg() function (first):

DAvg("[[COLOR="Blue"]FieldToAverage[/COLOR]]", "[[COLOR="blue"]SourceQuery[/COLOR]]", "[[COLOR="blue"]ID[/COLOR]] <=" & [[COLOR="blue"]ID[/COLOR]])

SourceQuery must be a query that's sorted by the ID.

Get that working and we'll improve on the function.

 

[fat controller]

And on the version just knocked up, it works (although I had the average box bound to a field on the table - would that be the thing to do?)

 

[vbaInet]

Query or Control Source, whichever way you choose.

 

[fat controller]

It seems to be getting tripped up by the fact that the value is being entered from a combo....

Really confused now.

 

[vbaInet]

I don't see any combo boxes on your form.

Can you replicate what's in your current db and upload. Also include the DAvg function so I know what fields you want to average on and how.

 

[fat controller]

Right - getting somewhere I think.

If I type the values into the text boxes, the running average works beautifully. If the value is put into the textbox from a selection on a combo box, it ceases to work?

 

[fat controller]

This is pretty much what I am looking for

 

[vbaInet]

That's the code I gave you in my first post. So it's not a running average, it's just an average based on field values. And you don't save calculated values so you will need to substitute the Control Source of the control to the code I gave in my first post.

 

[fat controller]

That's the code I gave you in my first post. So it's not a running average, it's just an average based on field values. And you don't save calculated values so you will need to substitute the Control Source of the control to the code I gave in my first post.

Sorry, I led myself up a path and kept on wandering (told you I had my thick head on today)

It's sort of working now, but it is taking the average including the zeros - so if two fields are populated with 1.2, it is showing the average as 0.6 when it should be 1.2; is there any way to get it to completely ignore zeros or null values?

 

[vbaInet]

We all have our moments.

The denominator is what you need to tackle so add them up in the denominator and convert

[COLOR="Blue"]...[/COLOR] [COLOR="Red"]/ [/COLOR]Abs(Nz([COLOR="blue"]Control1[/COLOR], 0)<>0) + Abs(Nz([COLOR="blue"]Control2[/COLOR], 0)<>0) [COLOR="blue"]+ ...etc[/COLOR]

Meaning if a control is Null or Zero, return Zero, otherwise return 1. When you add these up it will divide properly.

 

[fat controller]

We all have our moments.

The denominator is what you need to tackle so add them up in the denominator and convert

[COLOR=Blue]...[/COLOR] [COLOR=Red]/ [/COLOR]Abs(Nz([COLOR=blue]Control1[/COLOR], 0)<>0) + Abs(Nz([COLOR=blue]Control2[/COLOR], 0)<>0) [COLOR=blue]+ ...etc[/COLOR]

Meaning if a control is Null or Zero, return Zero, otherwise return 1. When you add these up it will divide properly.

Blimey, I am really making this look like hard work - sorry, but I am still getting it wrong somewhere.

=(Nz([EWT1],0)+Nz([EWT2],0)+Nz([EWT3],0)+Nz([EWT4],0))/Abs(Nz([EWT1],0)<>0)+Abs(Nz([EWT2],0)<>0)+Abs(Nz([EWT3],0)<>0)+Abs(Nz([EWT4],0)<>0)

With four fields showing 1.2, it is showing the average as 7.8? I can't even work out what it is doing to arrive at that result?

 

[vbaInet]

=(Nz([EWT1],0)+Nz([EWT2],0)+Nz([EWT3],0)+Nz([EWT4],0))/[COLOR="red"]([/COLOR]Abs(Nz([EWT1],0)<>0)+Abs(Nz([EWT2],0)<>0)+Abs(Nz([EWT3],0)<>0)+Abs(Nz([EWT4],0)<>0)[COLOR="Red"])[/COLOR]

See both parentheses?

 

[fat controller]

See both parentheses?

Did you hear the penny drop from where you are? It did so with a bit of a crash

What an eejit I am - of course it wasn't going to work as it was dividing by the first one then adding the rest on! Doh!

 

[vbaInet]

I did

 

[fat controller]

Different result this time - I am now getting #Name? in the text box?