CODEWITHSUNDEEP
ms-accessvba
Steven
Steven

Reputation: 155

Handling NULL = NULL comparisons in Access VBA

I have a problem with NULL = NULL returning NULL. I want it to return True. After a bit of research I found a line that I thought would work..

set ansi_nulls off

I quickly learned that Access doesn't recognize it. So here I am attempting to do nested conditions in Access and it's a complete nightmare.

Is there an easier way to handle this?

Upvotes: 0

Views: 2789

Answers (2)

Erik A
Erik A

Reputation: 32642

If you're doing a large amount of "equal or both null" comparisons, there are multiple solutions:

  1. If you're OK with 0 = Null resulting to true when comparing numbers, or "" = Null resulting to true when comparing strings, you can use:

    If Nz(Value1) = Nz(Value2) Then

This replaces all Nulls with 0 or "" dependent on the input type.

Consequences: Nz(Null) = Nz(Null) → True, 0 = Nz(Null) → True, "" = Nz(Null) → True, 1 = Nz(Null) → False, "a" = Nz(Null) → False

  1. Create a user-defined function to do the comparison for you

The function:

Public Function CompareWithNulls(Value1 As Variant, Value2 As Variant) As Boolean
    If IsNull(Value1) And IsNull(Value2) Then
        CompareWithNulls = True
    Else
        CompareWithNulls = Value1 = Value2
    End If
End Function

The use of the function:

If CompareWithNulls(Value1, Value2) Then

Upvotes: 2

Milk
Milk

Reputation: 2655

You can use the And keyword to test multiple conditions without nesting:

If IsNull(Value1) And IsNull(Value2) Then

You could create a method to compare two variables with a null check and then each IF only needs to call this function instead of the usual =

Upvotes: 0

Related Questions