469,159 Members | 1,526 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

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

2 2Bits

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

- 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:

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.

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).

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,

P.S. Attachment will be posted in a moment.
Jul 28 '21 #1
3 12798
2 2Bits
Please see attached zip file containing the 3 ACCDB version + 1 PPT to illustrate the process.
Attached Files
File Type: zip Process Flaw.zip (307.3 KB, 12 views)
Jul 28 '21 #2
289 Expert 256MB
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.

Also crossposted at https://www.access-programmers.co.uk...s-null.318847/ where a full solution was provided
Jul 28 '21 #3
32,167 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. If 32 > Null Then
  2.     'Y route.
  3. Else
  4.     'Z Route.
  5. 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 :
Expand|Select|Wrap|Line Numbers
  1. If 32 < Null Then
  2.     'Y route.
  3. Else
  4.     'Z Route.
  5. 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 :
Expand|Select|Wrap|Line Numbers
  1. VBA
  2. If 32 > Nz(X, 0) Then ...
  3. If Not IsNull(X) Then If 32 > X Then ...
  4. SQL
  5. If 32 > Nz(X, 0) Then ...
  6. 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.
Expand|Select|Wrap|Line Numbers
  1. VBA
  2. If Not IsNull(X) And (32 > X) Then ...
  3. SQL
  4. 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.
Jul 29 '21 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Bosconian | last post: by
34 posts views Thread by Srinu | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.