473,224 Members | 1,348 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,224 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 13182
skydivetom
3 2Bits
Please see attached zip file containing the 3 ACCDB version + 1 PPT to illustrate the process.
Jul 28 '21 #2
isladogs
451 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)...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
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: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.