VBA function that compare a BEFORE/AFTER table -- change from NULL to some other value not picked up though

**skydivetom** · Jul 28 '21, 09:14 PM

Please see attached zip file containing the 3 ACCDB version + 1 PPT to illustrate the process.

**isladogs** · Jul 28 '21, 09:57 PM

Crossposted at https://www.utteraccess.com/topics/2.../posts/2784909

This follows a similar question originally posted at https://www.accessforums.net/showthread.php?t=84088 where the OP has received extensive help already.

EDIT:
Also crossposted at https://www.access-programmers.co.uk...s-null.318847/ where a full solution was provided

**NeoPa** · Jul 29 '21, 02:52 PM

Hi Tom.

While I may not be happy to devote the large amounts of time & effort that would almost certainly be required for such a full question which alludes to multiple attached files, bearing in mind you already have an answer provided elsewhere, I might try to explain the area where I guess you may be struggling - to whit the difficulty sometimes experienced when trying to deal with Null values.

Null values are essentially more a lack of a value than any actual type of value. They are also very often associated with something called Null-Propagation which means that when anything which processes values is provided even one input parameter which is Null, then the output is always Null.

This includes simple comparison of values. For instance the following doesn't result in True or False - but Null :

Code:

32 > Null

However, and this is where many people get thoroughly misled, when used with comparison functions or operators Null will always be treated as False. Thus :

Code:

If 32 > Null Then
    'Y route.
Else
    'Z Route.
End If

The Z route would be the one that's followed. However, and this is where people get confused, the reverse of that ALSO causes the Z route to be followed :

Code:

If 32 < Null Then
    'Y route.
Else
    'Z Route.
End If

To get around this situation where logic seems to be suspended we include in our logic one, or more, checks where a Null is actually converted sensibly to a usable value. Here are some examples where X is a reference to something which may be Null :

Code:

[U][B]VBA[/B][/U]
If 32 > Nz(X, 0) Then ...
If Not IsNull(X) Then If 32 > X Then ...
[U][B]SQL[/B][/U]
If 32 > Nz(X, 0) Then ...
If X Is Not Null Then If 32 > X Then ...

Note that the following will fail as (True/False Or Null) & (True/False And Null) also both suffer from Null-Propagation so would result in Null rather than either Boolean value.

Code:

[U][B]VBA[/B][/U]
If Not IsNull(X) And (32 > X) Then ...
[U][B]SQL[/B][/U]
If (X Is Not Null) And (32 > X) Then ...

This leaves you needing to pay special attention whenever comparing values when any of the values included may evaluate to Null.

VBA function that compare a BEFORE/AFTER table -- change from NULL to some other value not picked up though

VBA function that compare a BEFORE/AFTER table -- change from NULL to some other value not picked up though

Comment

Comment

Comment