473,288 Members | 1,745 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

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

3 2Bits
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.
Jul 28 '21 #1
3 13187
skydivetom
3 2Bits
Please see attached zip file containing the 3 ACCDB version + 1 PPT to illustrate the process.
Jul 28 '21 #2
isladogs
454 Expert Mod 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.

EDIT:
Also crossposted at https://www.access-programmers.co.uk...s-null.318847/ where a full solution was provided
Jul 28 '21 #3
NeoPa
32,554 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

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

Similar topics

3
by: cylin | last post by:
Dear all, I met a char pointer problem. please help,thanks. How to change the the value which is pointed by a char pointer by a function? for example,...
6
by: Kevin Frey | last post by:
Hello, I have a table which contains some nullable columns. I want to write a single query, which can be prepared (ie. prepared statement), that can handle null or non-null values for the where...
4
by: Lachlan Hunt | last post by:
Hi, I was wondering if ::before and ::after pseudo-elements can apply to elements styled with the display: table-* properties. None of my tests worked in either Firefox or Opera, yet I could not...
8
by: KS | last post by:
Just to show some code to show the consept. <img id="date" onclick="javascript:show_calendar();" src="/PlexSysWeb/images/show-calendar.gif" width=20 height=18 border=0> What i want the...
5
by: Mike Nolan | last post by:
I have a before insert trigger that updates a value in another table. It appears that I cannot depend upon that update having taken place in an after insert trigger on the first table. (The one...
5
by: Rex | last post by:
Hi, I want to change a value in one table depending on the value(s) in another table. I am trying to achieve this in a form. to elaborate I have a many-to-many relationship between tables...
4
by: Bosconian | last post by:
I've been fighting with this for an hour. My form contains a hidden input with the value initially set to "". When a user clicks on the link, a function is called that updates the hidden form...
34
by: Srinu | last post by:
Hi all, Can we assign return value of a function to a global variable? As we know, main() will be the first function to be executed. but if the above is true, then we have a function call before...
1
by: gjangra | last post by:
i Need to convert value in following pattern in a report. For example: value = -7 after convert it will be value = Before 7 days if value in (+ve) value = 6 then value = after 6 days ...
6
by: Mihail | last post by:
Hello ! I have a form, (in a FE database) bound to a table (in a BE). You understand that is, in fact, a linked table, because I am in FE. In this form I have some labels (for bounded controls)...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.