VBA Not IsNull

Question

Working with some VBA code in Access that when a very specific set of conditions are met it will pop up an InputBox asking for a integer. So far so good.

Private Sub Command10_Click()

    If Not IsNull(mrf) Then
        If min <> max Then
            If qty <= min Then
                mrf = GetParamValue
            End If
        End If
    End If
    End Sub

The problem is that the Not IsNull seems to be ignored. I would like it to ask for a value to be entered unless a value is already present. This keeps firing off the InputBox as long as the min <> max and qty <= min conditions are met. What am I missing here?

Answer 1

No, the Not IsNull is working perfectly.

Remember, IsNull is a function which returns TRUE if the parameter passed to it is null, and false otherwise.

Your "If Not IsNull(mrf) Then" statement translates into english as "If mrf is not null then"

what that means is when mrf has a value, then you're processing the code inside the if statement. If you're wanting the inner code to fire when mrf IS null, then you need to remove the NOT from the statement.

Answer 2

My guess is that mrf isn't null, even if it's empty or something else. It could also be Nothing, which is different from null in VBA land (I think). Try running the code in the debugger and looking at the value of mrf. Depending on what mrf is, you can do a different test (like check len(mrf) or not isNothing(mrf) or if it's an integer, and it's init to zero, then mrf <> 0.... you get the idea. Hope that helps!

Answer 3

If mrf is Variant, then it's initially Empty, not Null. You therefore have to use IsEmpty() function.

Answer 4

You might be interested in this piece on VB6/VBA null/nothing/empty:

http://blogs.msdn.com/ericlippert/archive/2003/09/30/53120.aspx