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

[skydivetom]

Experts:

I need some assistance with identifying a process flaw in an overall well-working function. Allow me to provide some background first.

Background:
- I need to compare two (2) tables: "Before" and "After".
- Initially, the "After" table is a replica of the "Before" table.
- At some point records will be updated only in the "After" table.
- Given that my actual tables may have 50+ fields and 20k plus records, I need to be able to readily identify what value was changed in the "After" table.

Attached are three (3) version of the same *sample* database. Please follow the steps as illustrated below:

Version_01:
===========
a. Open DB... in this version both tables "tbl_01_Before" and "tbl_02_After" are identical... that is, no changes have been applied to the "After" table.
b. Open form "F01_MainMenu" and click command button "View Log (Value Changes)".
c. Table "tbl_Log_ValueChanges" opens with zero (0) records.
d. Based c., this is the **correct** output given that both source tables are identifical.

Version_02:
===========
e. For testing purposes, I modified five (5) records in table [tbl_02_After]. To make it very obvious, I used 7-digit values (e.g., "1111111", "2222222", "3333333", "4444444", "5555555".
f. Open v02 and follow steps a:b.
h. Now, given the value changes, the log file shows those 5 records (incl. record ID, fieldname, before/after values).
i. AGAIN, this works great!!!

Now, the issue lies in version 03 (which is a copy of v02).

Version_03:
===========
j. Again, for testing purposes, I made two (2) additional changes to the table [tbl_02_After].
k. Specifically, I added values "88888888" in record ID #7 (field [INJ_INJURY_TYPE_TIER2_ID]) and "999999999" in record ID #15 (field [INJ_BODY_PART_TIER1_ID]).
l. Now, please note that in table [tbl_01_Before], the 'before' values for the "88888888" AND "999999999" were both = NULL.
m. So, since a value changed from NULL to something else, these two additional records should also be captured by the VBA function.
m. Unfortunately though, the log only shows the 5 records (version 2 change) but NOT those 2 records where value was previously = NULL.

My question:
How should the VBA be modified in module "modCompareTables" so that a value change from NULL to something else will also be displayed/updated in tbl_Log_ValueChanges.

P.S. Besides the 3 ACCDB version, I have also included a PPT which illustrates the process for the 3 versions.

Thank you for your help in advance,
Tom

P.S. Attachment will be posted in a moment.

[skydivetom]

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

[isladogs]

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]

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 :

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 :

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 :

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 :

VBA
If 32 > Nz(X, 0) Then ...
If Not IsNull(X) Then If 32 > X Then ...
 SQL
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.

VBA
If Not IsNull(X) And (32 > X) Then ...
 SQL
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.