在Excel(VBA(中,我正试图在2个Access数据库上运行查询。我正在比较来自不同数据库的2个表,并列出两者之间的差异("查找未匹配的查询"(
以下查询运行时没有出现错误。
SELECT x.* FROM (SELECT * FROM TBL IN 'I:test1.mdb') x LEFT JOIN
(SELECT * FROM TBL IN 'I:testtest2.mdb') y ON (x.fldId = y.fldId) AND (x.fldTxt = y.fldTxt)
WHERE (((y.fldId) Is Null))
它的问题是Null值没有得到正确的比较,最终会得到结果,尽管两个数据库表中的记录是相等的。我发现Nz-function
可以解决这个问题
SELECT x.* FROM (SELECT * FROM TBL IN 'I:test1.mdb') x LEFT JOIN
(SELECT * FROM TBL IN 'I:testtest2.mdb') y ON (Nz(x.fldId = y.fldId)) AND (Nz(x.fldTxt = y.fldTxt))
WHERE (((y.fldId) Is Null))
然而,这在Access中有效,但在尝试从Excel VBA运行它时出错Run-time error '-2147217900 (80040e14)': [Microsoft][ODBC Excel Driver] Undefined function 'Nz' in expression
我选择了以下库:
1-Visual Basic For Applications
2-Microsoft Excel 16.0对象库
3-OLE Automation
4-Microsoft Office 16.0对象图书馆
5-Microsoft Forms 2.0对象图书馆
6-Microsoft ActiveX数据对象6.1库
7-Microsoft Access 16.0对象文库
8-Microsoft Office 160Access数据库引擎对象图书馆
我的问题是:
这能解决吗?或者有其他方法可以解决吗?
这里有一个替代品:
' 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