Type#! Error

[dmyoungsal]

I have a textbox containing this formula:

=Nz(((([txtAvgMPH1]+[txtAvgMPH2]+[txtAvgMPH3]+[txtAvgMPH4])/4),Nz(([txtAvgMPH1]+[txtAvgMPH2]+[txtAvgMPH3])/3),Nz(([txtAvgMPH1]+[txtAvgMPH2])/2),[txtAvgMPH1]))

nothing was displaying after I filled in the associated textboxes on my form. I put a macro in the Onchange event and when I open my form and filled in the text boxes, the Type#! error showed up.

Why? (Your help is appreciated)

 

[sneuberg]

Your expression has a syntax error. The parenthesis are mismatched somewhere. I've never seen Nz chained in the fashion you are attempting and it makes me wonder what you are trying to do and if the IIF function would be better. Anyway I think the best way to sort out something this complex is just to put it in a subroutine in a module so that you get the benefit of IntelliSense as you create the expression. I didn't this in the subroutine that follows and and was able to able to quickly develop an expression (the second uncommented one) without syntax error. I have no idea if this is what you were looking for as I can't figure out what you are trying to do.

What are you trying to do?

Private Sub TestMPH()

Dim txtAvgMPH1 As Variant
Dim txtAvgMPH2 As Variant
Dim txtAvgMPH3 As Variant
Dim txtAvgMPH4 As Variant
Dim Ans As Variant

'Ans = = Nz(([txtAvgMPH1]+[txtAvgMPH2]+[txtAvgMPH3]+[txtAvgMPH4])/4 ,Nz( ([txtAvgMPH1]+[txtAvgMPH2]+[txtAvgMPH3])/3, Nz(([txtAvgMPH1]+[txtAvgMPH2])/2 ,[txtAvgMPH1]) ) )

Ans = Nz(([txtAvgMPH1] + [txtAvgMPH2] + [txtAvgMPH3] + [txtAvgMPH4]) / 4, Nz(([txtAvgMPH1] + [txtAvgMPH2] + [txtAvgMPH3]) / 3, Nz(([txtAvgMPH1] + [txtAvgMPH2]) / 2, [txtAvgMPH1])))

End Sub

 

[dmyoungsal]

Since posting, my problem, I went down the path of using an IIF statement. By and large, it works, except when all the fields are not complete.

I am writing a form that will help our company estimate transportation costs.
I have two combo fields from which I can select equipment types (more informational than anything) and Zone (essentially miles - think of a target with rings, These are bands (0-20 miles, 21-40 miles, etc)

After I select the type, enter the quantity and select a Zone, using DLOOKUP, I get the miles and the estimated average speed. Along with a couple other fields, I can calculate our costs.

My problems come from when I do not fill all the fields (meaning, many loads will have one equipment type (possibly 4 max) and others will have a mix of types (max of 4). Unless I fill all fields, my calculations do not work. My IIF statement is and attempt to calculate the average of Average Speeds so I can calculate the total costs.

My if statement starts out looking at txtAvgMPH4 and if not blank,. adds the four average speeds together and divides by 4, the looks at txtAvgMPH3 and if not blank, add the top three together and divides by three, etc....

This is all good until I change Zone4 to blank, and the dllookup clears, and my iif statement begins to fail.

 

[sneuberg]

Maybe something like the function below would work for you. You could put it in your form's module and then put

=Average4(txtAvgMPH1, txtAvgMPH2, txtAvgMPH3, txtAvgMPH4)

in the textbox control source.

Private Function Average4(x1 As Variant, x2 As Variant, x3 As Variant, x4 As Variant) As Variant

Dim Denominator As Double

Denominator = 0
If Not IsNull(x1) Then
    Denominator = Denominator + 1
End If
If Not IsNull(x2) Then
    Denominator = Denominator + 1
End If
If Not IsNull(x3) Then
    Denominator = Denominator + 1
End If
If Not IsNull(x4) Then
    Denominator = Denominator + 1
End If

If Denominator = 0 Then
    Average4 = Null 'Or whatever you want
Else
    Average4 = (CDbl(Nz(x1)) + CDbl(Nz(x2)) + CDbl(Nz(x3)) + CDbl(Nz(x4))) / Denominator
End If


End Function

 

[dmyoungsal]

if found a solution for this:

=( Nz(txtAvgMPH1, 0) + Nz(txtAvgMPH2, 0) + Nz(txtAvgMPH3, 0) + Nz(txtAvgMPH4, 0))/ ( If(IsNull(txtAvgMPH1), 0 ,1) + If(IsNull(txtAvgMPH2), 0 ,1) + If(IsNull(txtAvgMPH3), 0 ,1) + If(IsNull(txtAvgMPH4), 0 ,1))

 

[sneuberg]

if found a solution for this:

=( Nz(txtAvgMPH1, 0) + Nz(txtAvgMPH2, 0) + Nz(txtAvgMPH3, 0) + Nz(txtAvgMPH4, 0))/ ( If(IsNull(txtAvgMPH1), 0 ,1) + If(IsNull(txtAvgMPH2), 0 ,1) + If(IsNull(txtAvgMPH3), 0 ,1) + If(IsNull(txtAvgMPH4), 0 ,1))

I tested this solution and it doesn't work for these reasons:

• The Ifs should be IIFs
• If textboxes are added like this you will get concatenation rather than addition. They need to be converted to a number by CDbl for example
• If all of the textboxes are empty you get a divide by zero error. You could fix this with an additional Nz in the denominator expression.

 

[dmyoungsal]

Thank you,

I caught the error in the IIF statements and I have the formula working fine.