Match Null or Empty and Case Sensitive Changes in Records 1

[patriciaxxx]

I have the following code which writes the changes between to recordsets into a third table, and it works, except for 2 things which I can’t work out.

1 It doesn’t retrieve changes if there is a null or empty value.

2 It doesn’t retrieve Case Sensitive changes.

Any help modifying code to achieve this would be appreciated.

Option Compare Database
Option Explicit

'This requires a reference to the Microsoft DAO 3.6 Object Library.
Function CompareTables()
'Loop through each field in each record in the first table and compare it to the same field
'in the new table via the Primary Key.
Dim db As DAO.Database
Dim rstOLD As DAO.Recordset
Dim rstNEW As DAO.Recordset
Dim rstResults As DAO.Recordset
Dim fldName As String

Set db = CurrentDb
Set rstResults = db.OpenRecordset("tblResults") 'Results table.
Set rstOLD = db.OpenRecordset("tblBenchmarkSettings") 'Old table.
Do Until rstOLD.EOF
  Set rstNEW = db.OpenRecordset("SELECT * " & _
    "FROM tblBenchmarkSettings_2 " & _
    "WHERE [NumberID]=" & rstOLD![NumberID]) '='" & rstOLD![pk string] & "';") 'New table.
  
  If Not rstNEW.EOF And Not rstNEW.BOF Then
    Dim i As Long
    For i = 0 To rstOLD.Fields.Count - 1
      fldName = rstOLD(i).Name
      'Contents of field have changed.
      If rstOLD(fldName) <> rstNEW(fldName) Then
          'Write the differences to our table.
          With rstResults
          .AddNew
          !PrimaryKey = rstOLD!NumberID
          !fldName = fldName
          !OldValue = rstOLD.Fields(fldName)
          !NewValue = rstNEW.Fields(fldName)
          !DateDetected = Now()
          .Update
        End With
      End If
    Next i
  End If
  rstOLD.MoveNext
Loop

'Clean up.
rstOLD.Close
rstNEW.Close
rstResults.Close
Set rstOLD = Nothing
Set rstNEW = Nothing
Set rstResults = Nothing

End Function

 

[1DMF]

  If rstOLD(fldName) <> rstNEW(fldName) Then

I assume this errors every time it hits a NULL, as you cannot use standard comparison operators for checking NULL values.

No different than SQL where you must use 'IS NULL' or 'IS NOT NULL', in VB you must NULL check, ISNULL(variable) or nz(variable,"value if null")

so perhaps...

nz(rstOLD(fldName),"NULL") <> nz(rstNEW(fldName),"NULL")

With regard to case, Microsoft isn't case sensitive, unless you force it to be.

You could use

StrComp("a", "A", vbBinaryCompare)

string comparison using vbBinaryCompare, coupled with the null check, this may give you your desired result.

If StrComp(nz(rstOLD(fldName),"NULL"), nz(rstNEW(fldName),"NULL"), vbBinaryCompare) <> 0 Then

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[patriciaxxx]

That’s sorted it.
Works a treat.
Thank you 1DMF

 

[Andrzejek]

You can also use this fix for NULLs.
Instead of this:

If rstOLD(fldName) <> rstNEW(fldName) Then

Just add this:

If rstOLD(fldName) [blue]& ""[/blue] <> rstNEW(fldName) [blue]& ""[/blue] Then

Have fun.

---- Andy

 

[1DMF]

No problem, you are most welcome. Glad you got it working.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[patriciaxxx]

Thank you Andrzejek
That’s an interesting fix for Nulls which works.

 

[PHV]

That’s an interesting fix for Nulls which works
Sorry to disagree.
If one is null and the other is zero length you don't catch the difference.

 

[1DMF]

Yes concatenation of NULL with an empty string = empty string.

Though personally I prefer to use the NZ function as it is clearer your intentions when you read the code.

And as PHV states, what then when you actually have a value = empty string and it used to be NULL?

They would both show as empty string using concatenation and so it doesn't appear the value changed.

If you want to know if something is NULL, perform a NULL check, otherwise you can end up with undesired results and can become a nightmare trying to debug.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[patriciaxxx]

Wow, not always is something what it at first appears to be.
Thank you PHV & 1DMF for making that clear to me.