CODEWITHSUNDEEP
sqlexcelvbams-access
MK01111000
MK01111000

Reputation: 832

Is there a alternative for the SQL 'Nz'-function

In Excel (VBA) I am trying to run a query on 2 Access databases. I am comparing 2 tables from different databases and list differences between the two ('Find Unmacthed Query')
The following query runs without error.

SELECT x.* FROM (SELECT * FROM TBL IN 'I:\test1.mdb') x LEFT JOIN 
(SELECT * FROM TBL IN 'I:\test\test2.mdb') y ON (x.fldId = y.fldId) AND (x.fldTxt = y.fldTxt)   
WHERE (((y.fldId) Is Null))

The problem with it is that Null values aren't compared correctly and will end up in the final result although the records from both database tables are equal. I figured out that this can be solved with the Nz-function

SELECT x.* FROM (SELECT * FROM TBL IN 'I:\test1.mdb') x LEFT JOIN 
(SELECT * FROM TBL IN 'I:\test\test2.mdb') y ON (Nz(x.fldId = y.fldId)) AND (Nz(x.fldTxt = y.fldTxt))  
WHERE (((y.fldId) Is Null))

This however works in Access, but gives me an error when trying to run it from Excel VBA
Run-time error '-2147217900 (80040e14)': [Microsoft][ODBC Excel Driver] Undefined function 'Nz' in expression

I have the following libraries selected:
1- Visual Basic For Applications
2- Microsoft Excel 16.0 Object Library
3- OLE Automation
4- Microsoft Office 16.0 Object Library
5- Microsoft Forms 2.0 Object Library
6- Microsoft ActiveX Data Objects 6.1 Library
7- Microsoft Access 16.0 Object Library
8- Microsoft Office 16.0 Access database engine Object Library

My question is:
Can this be solved or is there an alternative to accomplish the same?

Upvotes: 0

Views: 749

Answers (1)

Gustav
Gustav

Reputation: 55961

Here's a replacement:

' Replacement for the function Application.Nz() of Access.
'
' Returns by default Empty if argument Value is Null and
' no value for argument ValueIfNull is passed.
'
' 2020-10-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Nz( _
    ByRef Value As Variant, _
    Optional ByRef ValueIfNull = Empty) _
    As Variant

    Dim ValueNz     As Variant

    If Not IsEmpty(Value) Then
        If IsNull(Value) Then
            ValueNz = ValueIfNull
        Else
            ValueNz = Value
        End If
    End If
        
    Nz = ValueNz
    
End Function

Upvotes: 0

Related Questions