vba NVL 函数出错，是否有类似 NVL 的等效函数

Error in NVL function,is there any equivalent function like NVL

提问by arun_roy

The below code is written VBScript.but the line Set rs = cmd.Executethrowing an error saying that NVLis not defined. So can you suggest an equivalent of such "NVL"

下面的代码是用 VBScript 编写的。但是该行Set rs = cmd.Execute抛出一个错误，表示NVL未定义。那么你能建议一个相当于这样的“NVL”吗？

        Option Explicit

        Dim conn, cmd, rs
         Dim clauses(34), i
    Dim xlApp, xlBook
    Dim tempDate,LenDate


    Set conn = CreateObject("ADODB.Connection")
    With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=""D:\AravoVB\GE_Wing_To_Wing_Report - Copy.xlsx"";" & _
        "Extended Properties=""Excel 12.0;HDR=Yes"""
    .Open
    End With
    tempDate="[Business Process ID],"
    For i = 0 To 34
    clauses(i) = "[Task" & i + 1 & " Start Date] > [Task" & i + 2 & " Start Date]"
    tempDate=tempDate & "NVL([Task" & i + 1 & " Start Date],sysdate+"& i &"),"
    Next
    LenDate=Len(tempDate)-1
    tempDate=Mid(tempDate,1,LenDate)
    MsgBox(tempDate)

    Set cmd = CreateObject("ADODB.Command")
    cmd.CommandText = "SELECT " & tempDate &" FROM [GEWingToWingMay25$] WHERE [Business Process ID] NOT IN (" & "SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE " & Join(clauses, " AND ") & ")"
    MsgBox(cmd.CommandText)
    cmd.ActiveConnection = conn 
    Set rs = cmd.Execute

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Add
    xlBook.Sheets(1).Range("A1").CopyFromRecordset cmd.Execute

EDIT:

编辑：

      For i = 0 To 1
      clauses(i) = "IIf(IsNull([Task" & i + 1 & " Start Date]),sysdate+"& i &",[Task" & i + 1 & " Start Date]) > IIf(IsNull([Task" & i + 2 & " Start Date]),sysdate+"& i &",[Task" & i + 2 & " Start Date])"
'tempDate=tempDate & "NVL([Task" & i + 1 & " Start Date],sysdate+"& i &"),"
      Next

Error: Getting No value given for one or more required parameters

错误：获取No value given for one or more required parameters

MsgBox(cmd.CommandText):

MsgBox(cmd.CommandText):

SQL

SQL

         `SELECT * FROM [GEWingToWingMay25$] WHERE [Business Process ID] NOT IN (SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE IIf(IsNull([Task1 Start Date]),Date()+0,[Task1 Start Date]) < IIf(IsNull([Task2 Start Date]),Date()+0,[Task2 Start Date]) OR IIf(IsNull([Task2 Start Date]),Date()+1,[Task2 Start Date]) < IIf(IsNull([Task3 Start Date]),Date()+1,[Task3 Start Date]) OR IIf(IsNull([Task3 Start Date]),Date()+2,[Task3 Start Date]) < IIf(IsNull([Task4 Start Date]),Date()+2,[Task4 Start Date]) OR IIf(IsNull([Task4 Start Date]),Date()+3,[Task4 Start Date]) < IIf(IsNull([Task5 Start Date]),Date()+3,[Task5 Start Date]) OR IIf(IsNull([Task5 Start Date]),Date()+4,[Task5 Start Date]) < IIf(IsNull([Task6 Start Date]),Date()+4,[Task6 Start Date]) OR IIf(IsNull([Task6 Start Date]),Date()+5,[Task6 Start Date]) < IIf(IsNull([Task7 Start Date]),Date()+5,[Task7 Start Date]) OR IIf(IsNull([Task7 Start Date]),Date()+6,[Task7 Start Date]) < IIf(IsNull([Task8 Start Date]),Date()+6,[Task8 Start Date]) OR IIf(IsNull([Task8 Start Date]),Date()+7,[Task8 Start Date]) < IIf(IsNull([Task9 Start Date]),Date()+7,[Task9 Start Date]) OR IIf(IsNull([Task9 Start Date]),Date()+8,[Task9 Start Date]) < IIf(IsNull([Task10 Start Date]),Date()+8,[Task10 Start Date]) OR IIf(IsNull([Task10 Start Date]),Date()+9,[Task10 Start Date]) < IIf(IsNull([Task11 Start Date]),Date()+9,[Task11 Start Date]) OR IIf(IsNull([Task11 Start Date]),Date()+10,[Task11 Start Date]) < IIf(IsNull([Task12 Start Date]),Date()+10,[Task12 Start Date]) OR IIf(IsNull([Task12 Start Date]),Date()+11,[Task12 Start Date]) < IIf(IsNull([Task13 Start Date]),Date()+11,[Task13 Start Date]) OR IIf(IsNull([Task13 Start Date]),Date()+12,[Task13 Start Date]) < IIf(IsNull([Task14 Start Date]),Date()+12,[Task14 Start Date]) OR IIf(IsNull([Task14 Start Date]),Date()+13,[Task14 Start Date]) < IIf(IsNull([Task15 Start Date]),Date()+13,[Task15 Start Date]) OR IIf(IsNull([Task15 Start Date]),Date()+14,[Task15 Start Date]) < IIf(IsNull([Task16 Start Date]),Date()+14,[Task16 Start Date]) OR IIf(IsNull([Task16 Start Date]),Date()+15,[Task16 Start Date]) < IIf(IsNull([Task17 Start Date]),Date()+15,[Task17 Start Date]) OR IIf(IsNull([Task17 Start Date]),Date()+16,[Task17 Start Date]) < IIf(IsNull([Task18 Start Date]),Date()+16,[Task18 Start Date]) OR IIf(IsNull([Task18 Start Date]),Date()+17,[Task18 Start Date]) < IIf(IsNull([Task19 Start Date]),Date()+17,[Task19 Start Date]) OR IIf(IsNull([Task19 Start Date]),Date()+18,[Task19 Start Date]) < IIf(IsNull([Task20 Start Date]),Date()+18,[Task20 Start Date]) OR IIf(IsNull([Task20 Start Date]),Date()+19,[Task20 Start Date]) < IIf(IsNull([Task21 Start Date]),Date()+19,[Task21 Start Date]) OR IIf(IsNull([Task21 Start Date]),Date()+20,[Task21 Start Date]) < IIf(IsNull([Task22 Start Date]),Date()+20,[Task22 Start Date]) OR IIf(IsNull([Task22 Start Date]),Date()+21,[Task22 Start Date]) < IIf(IsNull([Task23 Start Date]),Date()+21,[Task23 Start Date]) OR IIf(IsNull([Task23 Start Date]),Date()+22,[Task23 Start Date]) < IIf(IsNull([Task24 Start Date]),Date()+22,[Task24 Start Date]) OR IIf(IsNull([Task24 Start Date]),Date()+23,[Task24 Start Date]) < IIf(IsNull([Task25 Start Date]),Date()+23,[Task25 Start Date]) OR IIf(IsNull([Task25 Start Date]),Date()+24,[Task25 Start Date]) < IIf(IsNull([Task26 Start Date]),Date()+24,[Task26 Start Date]) OR IIf(IsNull([Task26 Start Date]),Date()+25,[Task26 Start Date]) < IIf(IsNull([Task27 Start Date]),Date()+25,[Task27 Start Date]) OR IIf(IsNull([Task27 Start Date]),Date()+26,[Task27 Start Date]) < IIf(IsNull([Task28 Start Date]),Date()+26,[Task28 Start Date]) OR IIf(IsNull([Task28 Start Date]),Date()+27,[Task28 Start Date]) < IIf(IsNull([Task29 Start Date]),Date()+27,[Task29 Start Date]) OR IIf(IsNull([Task29 Start Date]),Date()+28,[Task29 Start Date]) < IIf(IsNull([Task30 Start Date]),Date()+28,[Task30 Start Date]) OR IIf(IsNull([Task30 Start Date]),Date()+29,[Task30 Start Date]) < IIf(IsNull([Task31 Start Date]),Date()+29,[Task31 Start Date]) OR IIf(IsNull([Task31 Start Date]),Date()+30,[Task31 Start Date]) < IIf(IsNull([Task32 Start Date]),Date()+30,[Task32 Start Date]) OR IIf(IsNull([Task32 Start Date]),Date()+31,[Task32 Start Date]) < IIf(IsNull([Task33 Start Date]),Date()+31,[Task33 Start Date]) OR IIf(IsNull([Task33 Start Date]),Date()+32,[Task33 Start Date]) < IIf(IsNull([Task34 Start Date]),Date()+32,[Task34 Start Date]) OR IIf(IsNull([Task34 Start Date]),Date()+33,[Task34 Start Date]) < IIf(IsNull([Task35 Start Date]),Date()+33,[Task35 Start Date]) OR IIf(IsNull([Task35 Start Date]),Date()+34,[Task35 Start Date]) < IIf(IsNull([Task36 Start Date]),Date()+34,[Task36 Start Date]))`

EDIT

编辑

OptiOn Explicit

选项显式

Dim conn, cmd, rs
     Dim clauses(34), i
Dim xlApp, xlBook
Dim tempDate,LenDate


Set conn = CreateObject("ADODB.Connection")
With conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=""D:\AravoVB\Final Scripts\GE_Wing_To_Wing_Report - Copy.xlsx"";" & _
    "Extended Properties=""Excel 12.0;HDR=Yes"""
.Open
End With
'tempDate=""
For i = 0 To 34
clauses(i) = "IIf(IsNull([Task" & i + 1 & " Start Date]),Date()+"& i &",[Task" & i + 1 & " Start Date]) < IIf(IsNull([Task" & i + 2 & " Start Date]),Date()+"& i &",[Task" & i + 2 & " Start Date])"
tempDate=tempDate & "NVL([Task" & i + 1 & " Start Date],Date()+"& i &"),"
Next
'LenDate=Len(tempDate)-1
'tempDate=Mid(tempDate,1,LenDate)
MsgBox(tempDate)

Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "SELECT * FROM [GEWingToWingMay25$] WHERE [Business Process ID] NOT IN (" & "SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE " & Join(clauses, " OR ") & ")"
MsgBox(cmd.CommandText)
cmd.ActiveConnection = conn 
Set rs = cmd.Execute

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
xlBook.Sheets(1).Range("A1").CopyFromRecordset cmd.Execute
'xlBook.Sheets(1).Cells(1,25).Value=cmd.CommandText

回答by bonCodigo

This answers your question on NVL, Nz Equivalent.

这回答了您关于 NVL, Nz Equivalent 的问题。

Just use IIF(IsNull())..

只需使用 IIF(IsNull())..

Because Nzis only supported within Access.

因为Nz仅在 Access 中受支持。

Here is the reference article :

这是参考文章：

The only non-intrinsic SQL functions available in JET database queries outside of Access are those found in the VBA library, whose objects are exposed for use via the underlying JET database engine (with consideration for certainsandbox restrictions). You can perform some crazy-complex calculations in your queries using functions like VBA'sIPmt()?and?PPmt(), and you can evaluate conditional logic using VBA's?IIf(), but you cannot?Nz()?a column to overcome a Null value, because Nz() is a method of the Access.Application object.

Access 之外的 JET 数据库查询中唯一可用的非内在 SQL 函数是在 VBA 库中找到的那些，其对象通过底层 JET 数据库引擎公开使用（考虑到某些沙盒限制）。您可以使用 VBA 的IPmt()?and?PPmt() 等函数在查询中执行一些疯狂复杂的计算，并且您可以使用 VBA 的?IIf() 评估条件逻辑，但您不能使用?Nz()? 列克服 Null 值，因为 Nz() 是 Access.Application 对象的一个​​方法。

So what are the alternatives to Nz()? Well, you could combine VBA's IIf() and?IsNull()?to do the same thing:

那么 Nz() 的替代方案是什么？好吧，你可以结合 VBA 的 IIf() 和?IsNull()? 来做同样的事情：

select IIf(IsNull(SomePossiblyNullField), '', SomePossiblyNullField) 
from SomeTable 

Or, if you're dealing with potentially Null string values, you can take advantage of the non-conforming JET SQL syntax's explicit?concatenation operator (&), which it shares with VBA, and pound an empty string onto your value. The following query will produce the same result as the previous one:

或者，如果您正在处理潜在的空字符串值，您可以利用不符合 JET SQL 语法的显式连接运算符 (&)，它与 ​​VBA 共享，并将空字符串重击到您的值上。以下查询将产生与前一个相同的结果：

select SomePossiblyNullField & '' 
from SomeTable 

I say non-conforming because in most cases combining Null with anything via an operator results in a Null; however, the concatenation operator works differently, treating Nulls like empty strings. Mathematical operators, like +, -, *, etc., do conform with standard tri-value Null logic in that their application to any Null value results in a Null expression, including the unfortunate case where + is applied to strings, which JET's SQL syntax and VBA still allow for historical reasons.

我说不符合是因为在大多数情况下，通过运算符将 Null 与任何内容结合会导致 Null；但是，连接运算符的工作方式不同，将 Null 视为空字符串。数学运算符，如 +、-、* 等，确实符合标准的三值 Null 逻辑，因为它们对任何 Null 值的应用都会导致 Null 表达式，包括将 + 应用于字符串的不幸情况，JET 的 SQL由于历史原因，语法和 VBA 仍然允许。

---

---

Here are possible reasons for the error: NO VALUE GIVEN FOR ONE OR MORE PARAMETERS.

以下是错误的可能原因：NO VALUE GIVEN FOR ONE OR MORE PARAMETERS。

Reference article:

参考文章：

• Reason 1: If the worksheet contain columnnames in the first row or not. If yes then the connection string should include the Extended Property HDR=YESotherwise to HDR=NO. Which you already have set.

• Reason 2: SQL Expressions and column names used in Whereclause. This seems to be the most possible cause of your error.

• 原因 1：工作表的第一行是否包含列名。如果是，则连接字符串应包括扩展属性，HDR=YES否则为HDR=NO。你已经设置了。

• 原因 2：Where子句中使用了 SQL 表达式和列名。这似乎是您出错的最可能原因。

Can you edit your query string as the following: SYSDATEneeds to be treated as an Excel Date() and add days using DateSerial()and then maintain the date format using Format().

您能否按如下方式编辑查询字符串： SYSDATE需要将其视为 Excel Date() 并使用添加天数DateSerial()，然后使用Format().

Format(DateSerial(Year(Date),Month(Date),Day(Date) + i), "yyyy-mm-dd")

Format(DateSerial(Year(Date),Month(Date),Day(Date) + i), "yyyy-mm-dd")

AS for the whereclause, may be it's best to use a Switchstatment and make sure add a default condition such as Date < Date + 1or as it deems.

对于where子句，最好使用Switch语句并确保添加默认条件，例如Date < Date + 1或 它认为。