NVL功能出现错误，是否有类似NVL的等效功能 [英] Error in NVL function,is there any equivalent function like NVL

查看：1490
本文介绍了NVL功能出现错误，是否有类似NVL的等效功能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

下面的代码是写VBScript.but行设置rs = cmd.Execute 抛出一个错误，说 NVL 没有定义。所以你可以建议一个等价的这样的NVL

The below code is written VBScript.but the line Set rs = cmd.Execute throwing an error saying that NVL is not defined. So can you suggest an equivalent of such "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

编辑： 

      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

错误 ：获取没有给出一个或多个所需参数的值

MsgBox（cmd.CommandText）：

SQL / strong>

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]))`

编辑

OptiOn Explicit

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


推荐答案



只需使用 IIF（IsNull（））..

因为 Nz 仅被支持在Access中。

Because Nz is only supported within Access.

以下是参考文章：

在Access之外的JET数据库查询中可用的唯一非内在SQL函数是找到的在VBA库中，其对象通过底层的JET数据库引擎（通过确定和框限制的考虑）来暴露。您可以使用VBA'sIPmt（）和PPmt（），等函数在查询中执行一些疯狂复杂的计算，您可以使用VBA的IIf（）来评估条件逻辑，但是不能Nz（）列克服因为Nz（）是Access.Application对象的一个​​方法。

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.

那么Nz（）的替代方法是什么？嗯，你可以结合VBA的IIf（）和IsNull（）来做同样的事情：

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

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

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

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: 

select SomePossiblyNullField & '' 
from SomeTable

我说不符合，因为在大多数情况下，将Null与任何东西组合通过一个运算符导致一个Null;然而，连接操作符的工作方式不同，像空字符串一样处理Null。数学运算符，如+， - ，*等，确实符合标准的三值空逻辑，因为它们的应用程序对任何Null值都会产生一个Null表达式，包括不适用于+应用于字符串的情况，JET的SQL语法和VBA仍然允许历史原因。

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.

参考文章：


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

原因2：中使用的SQL表达式和列名称其中子句。这似乎是您错误的最可能的原因。

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

您可以如下编辑您的查询字符串：
SYSDATE 需要被视为Excel Date（），并使用 DateSerial（）添加日期，然后维护日期格式使用格式（）。

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

格式（DateSerial（Year（Date） ，月份（日期），日（日期）+ i），yyyy-mm-dd）

$ c> where 子句，可能最好使用 Switch statment，并确保添加默认条件，如日期<日期+ 1 或按照其认为。

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

这篇关于NVL功能出现错误，是否有类似NVL的等效功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆