Trouble converting nulls to zeroes using Nz()

[TyroneB]

My function works except when either of two arguments, Frac_5_PARtbl or Frac_5, pass null values. If the nulls are changed to zeroes, the function works, so I've tried using the Nz() function to change the nulls to zero, but without success. The error in the immediate window is "compile error: expected: expression." Making the arguments optional didn't work, either.

Am I doing something wrong?

Here's the code:

Option Compare Database
Option Explicit

Public Function FigFract5(Distance_Feet As Single, Frac_5 As Variant, Frac_5_PARtbl As Variant) As Variant

Dim FigFract0 As Variant
Dim Frac_Distance As Single
Dim FracPar As Variant
Dim Frac As Variant
Frac = Nz(Frac_5, 0)
FracPar = Nz(Frac_5_PARtbl, 0)
FigFract0 = 95


If Frac > 0 Then

If Distance_Feet > 6600 Or Distance_Feet < 8580 Then
    Frac_Distance = 6600
ElseIf Distance_Feet = 8580 Or Distance_Feet = 9240 Then
    Frac_Distance = 7920
ElseIf Distance_Feet > 9240 Or Distance_Feet < 10770 Then
    Frac_Distance = 9240
ElseIf Distance_Feet > 10560 Or Distance_Feet < 11880 Then
    Frac_Distance = 10560
ElseIf Distance_Feet = 11880 Then
    Frac_Distance = 11220
End If

If Frac = FracPar And Frac > 0 Then
        FigFract0 = 95

ElseIf FracPar > Frac Then
    While FracPar >= Frac
        FracPar = FracPar - 0.01
        FigFract0 = (1 / FracPar * Frac_Distance / Distance_Feet) * 10 / 3 + FigFract0
    Wend

ElseIf FracPar < Frac Then
    While FracPar <= Frac
        FracPar = FracPar + 0.01
        FigFract0 = FigFract0 - (1 / FracPar * Frac_Distance / Distance_Feet) * 10 / 3
    Wend

End If

        FigFract5 = Round(FigFract0, 1)
        
ElseIf Frac = 0 Then FigFract5 = 0
End If

End Function

 

[Banana]

1) Exactly on which line do you get the error for an expected expression? It could Nz() or something else.

2) You might want to select using Select Case block instead of If..ElseIf...Else...block. It'll run faster and is much easier to implement

 

[TyroneB]

Thanks for the reply.

When I enter null values in the immediate window, the code isn't highlighted, so I can't say exactly where it's going wrong. But Nz is not causing the error, which occurs even without Nz. I added the Nz function hoping to solve the problem.

I got around the error by using IIF in the query that includes the function, but I still would like to know why the Nz didn't work.

Thanks for the tip about using Select Case.

 

[Banana]

You can step through the code by using breakpoints;

To insert a breakpoint, click on the gray margin to left of the code window, and a red dot will appear. Run the code, then the code at breakpoint will be highlighted. You can then use F8 to step one line at a time to see how it runs.

HTH.

 

[James Deckert]

Are you sure
>>If Distance_Feet > 6600 Or Distance_Feet < 8580 Then
Frac_Distance = 6600
is what you want. Every # will be either greater than 6600 or less than 8580. Don't you mean 'And' instead of 'Or'.

James

 

[TyroneB]

Yes, "and" is what I mean, but for some reason I put "or" in when writing the function, and since it seemed to work, I didn't change it. (Changing it does not solve the Nz problem.)

My problem is that the function does not handle null values. If you put nulls in as arguments in the immediate window, it doesn't work. I hoped Nz would solve that problem, but Nz doesn't work in the function itself. However, if you use it as a filter on the data before using the data, it does the job. This suggests that something's not set up correctly to get Nz to work within the VBA function.

Does Nz need a special reference library to work in VBA?

 

[Adeptus]

Why are they all declared as Variants?
Try declaring them as a numeric type.

Try what Banana suggested, set a breakpoint at the beginning of the function & step through to find what line is the problem.

 

[TyroneB]

The posted function has variant type because it didn't work with double or single.

The function below produces the same error as the above function when a null value is passed. It accepts everything except a null value.

Public Function ZEROtest(NullTest As Double) As Double

ZEROtest = Nz(NullTest)

End Function

But if I make the argument optional, it works:

Public Function ZEROtest(Optional NullTest As Double) As Double

ZEROtest = Nz(NullTest, 0)

End Function

Making the arguments optional in the original function also works. I had tried this before, but must have done something wrong because I couldn't get it to work at that time.

Is the use of optional arguments the only solution?

 

[Banana]

Erm, I don't think that was an optional argument-

Nz() requires two arguments; The value to be tested, and the value to replace if null. I've yet to use a Nz() without the second argument, so it'd be no surprise that a Nz() without the second argument (e.g. the zero) wouldn't go.

Edit= Noted that you were talking about the function; in which case, it's not relevent. The syntax for Nz() was incorrect when it wasn't optional then it was changed (and is correct) when you added an optional argument for that function.