title: Error in NVL function,is there any equivalent function like NVL
body: <p>The below code is written VBScript.but the line <code>Set rs = cmd.Execute</code> throwing an error saying that <code>NVL</code> is not defined. So can you suggest an equivalent of such "NVL"</p>
  
  <pre><code>        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"";" &amp; _
          "Extended Properties=""Excel 12.0;HDR=Yes"""
      .Open
      End With
      tempDate="[Business Process ID],"
      For i = 0 To 34
      clauses(i) = "[Task" &amp; i + 1 &amp; " Start Date] &gt; [Task" &amp; i + 2 &amp; " Start Date]"
      tempDate=tempDate &amp; "NVL([Task" &amp; i + 1 &amp; " Start Date],sysdate+"&amp; i &amp;"),"
      Next
      LenDate=Len(tempDate)-1
      tempDate=Mid(tempDate,1,LenDate)
      MsgBox(tempDate)
  
      Set cmd = CreateObject("ADODB.Command")
      cmd.CommandText = "SELECT " &amp; tempDate &amp;" FROM [GEWingToWingMay25$] WHERE [Business Process ID] NOT IN (" &amp; "SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE " &amp; Join(clauses, " AND ") &amp; ")"
      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
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/gGD9g.png" alt="Error in NZ"></p>
  
  <p><strong><em>EDIT:</em></strong></p>
  
  <pre><code>      For i = 0 To 1
        clauses(i) = "IIf(IsNull([Task" &amp; i + 1 &amp; " Start Date]),sysdate+"&amp; i &amp;",[Task" &amp; i + 1 &amp; " Start Date]) &gt; IIf(IsNull([Task" &amp; i + 2 &amp; " Start Date]),sysdate+"&amp; i &amp;",[Task" &amp; i + 2 &amp; " Start Date])"
  'tempDate=tempDate &amp; "NVL([Task" &amp; i + 1 &amp; " Start Date],sysdate+"&amp; i &amp;"),"
        Next
  </code></pre>
  
  <p><strong><em>Error</em></strong> : Getting <code>No value given for one or more required parameters</code></p>
  
  <p><code>MsgBox(cmd.CommandText):</code></p>
  
  <p><img src="https://i.stack.imgur.com/m13eq.png" alt="ADO Error"></p>
  
  <p><img src="https://i.stack.imgur.com/ZGwHa.png" alt="ADO Error2"></p>
  
  <p><strong><em>SQL</em></strong></p>
  
  <pre><code>         `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]) &lt; IIf(IsNull([Task2 Start Date]),Date()+0,[Task2 Start Date]) OR IIf(IsNull([Task2 Start Date]),Date()+1,[Task2 Start Date]) &lt; IIf(IsNull([Task3 Start Date]),Date()+1,[Task3 Start Date]) OR IIf(IsNull([Task3 Start Date]),Date()+2,[Task3 Start Date]) &lt; IIf(IsNull([Task4 Start Date]),Date()+2,[Task4 Start Date]) OR IIf(IsNull([Task4 Start Date]),Date()+3,[Task4 Start Date]) &lt; IIf(IsNull([Task5 Start Date]),Date()+3,[Task5 Start Date]) OR IIf(IsNull([Task5 Start Date]),Date()+4,[Task5 Start Date]) &lt; IIf(IsNull([Task6 Start Date]),Date()+4,[Task6 Start Date]) OR IIf(IsNull([Task6 Start Date]),Date()+5,[Task6 Start Date]) &lt; IIf(IsNull([Task7 Start Date]),Date()+5,[Task7 Start Date]) OR IIf(IsNull([Task7 Start Date]),Date()+6,[Task7 Start Date]) &lt; IIf(IsNull([Task8 Start Date]),Date()+6,[Task8 Start Date]) OR IIf(IsNull([Task8 Start Date]),Date()+7,[Task8 Start Date]) &lt; IIf(IsNull([Task9 Start Date]),Date()+7,[Task9 Start Date]) OR IIf(IsNull([Task9 Start Date]),Date()+8,[Task9 Start Date]) &lt; IIf(IsNull([Task10 Start Date]),Date()+8,[Task10 Start Date]) OR IIf(IsNull([Task10 Start Date]),Date()+9,[Task10 Start Date]) &lt; IIf(IsNull([Task11 Start Date]),Date()+9,[Task11 Start Date]) OR IIf(IsNull([Task11 Start Date]),Date()+10,[Task11 Start Date]) &lt; IIf(IsNull([Task12 Start Date]),Date()+10,[Task12 Start Date]) OR IIf(IsNull([Task12 Start Date]),Date()+11,[Task12 Start Date]) &lt; IIf(IsNull([Task13 Start Date]),Date()+11,[Task13 Start Date]) OR IIf(IsNull([Task13 Start Date]),Date()+12,[Task13 Start Date]) &lt; IIf(IsNull([Task14 Start Date]),Date()+12,[Task14 Start Date]) OR IIf(IsNull([Task14 Start Date]),Date()+13,[Task14 Start Date]) &lt; IIf(IsNull([Task15 Start Date]),Date()+13,[Task15 Start Date]) OR IIf(IsNull([Task15 Start Date]),Date()+14,[Task15 Start Date]) &lt; IIf(IsNull([Task16 Start Date]),Date()+14,[Task16 Start Date]) OR IIf(IsNull([Task16 Start Date]),Date()+15,[Task16 Start Date]) &lt; IIf(IsNull([Task17 Start Date]),Date()+15,[Task17 Start Date]) OR IIf(IsNull([Task17 Start Date]),Date()+16,[Task17 Start Date]) &lt; IIf(IsNull([Task18 Start Date]),Date()+16,[Task18 Start Date]) OR IIf(IsNull([Task18 Start Date]),Date()+17,[Task18 Start Date]) &lt; IIf(IsNull([Task19 Start Date]),Date()+17,[Task19 Start Date]) OR IIf(IsNull([Task19 Start Date]),Date()+18,[Task19 Start Date]) &lt; IIf(IsNull([Task20 Start Date]),Date()+18,[Task20 Start Date]) OR IIf(IsNull([Task20 Start Date]),Date()+19,[Task20 Start Date]) &lt; IIf(IsNull([Task21 Start Date]),Date()+19,[Task21 Start Date]) OR IIf(IsNull([Task21 Start Date]),Date()+20,[Task21 Start Date]) &lt; IIf(IsNull([Task22 Start Date]),Date()+20,[Task22 Start Date]) OR IIf(IsNull([Task22 Start Date]),Date()+21,[Task22 Start Date]) &lt; IIf(IsNull([Task23 Start Date]),Date()+21,[Task23 Start Date]) OR IIf(IsNull([Task23 Start Date]),Date()+22,[Task23 Start Date]) &lt; IIf(IsNull([Task24 Start Date]),Date()+22,[Task24 Start Date]) OR IIf(IsNull([Task24 Start Date]),Date()+23,[Task24 Start Date]) &lt; IIf(IsNull([Task25 Start Date]),Date()+23,[Task25 Start Date]) OR IIf(IsNull([Task25 Start Date]),Date()+24,[Task25 Start Date]) &lt; IIf(IsNull([Task26 Start Date]),Date()+24,[Task26 Start Date]) OR IIf(IsNull([Task26 Start Date]),Date()+25,[Task26 Start Date]) &lt; IIf(IsNull([Task27 Start Date]),Date()+25,[Task27 Start Date]) OR IIf(IsNull([Task27 Start Date]),Date()+26,[Task27 Start Date]) &lt; IIf(IsNull([Task28 Start Date]),Date()+26,[Task28 Start Date]) OR IIf(IsNull([Task28 Start Date]),Date()+27,[Task28 Start Date]) &lt; IIf(IsNull([Task29 Start Date]),Date()+27,[Task29 Start Date]) OR IIf(IsNull([Task29 Start Date]),Date()+28,[Task29 Start Date]) &lt; IIf(IsNull([Task30 Start Date]),Date()+28,[Task30 Start Date]) OR IIf(IsNull([Task30 Start Date]),Date()+29,[Task30 Start Date]) &lt; IIf(IsNull([Task31 Start Date]),Date()+29,[Task31 Start Date]) OR IIf(IsNull([Task31 Start Date]),Date()+30,[Task31 Start Date]) &lt; IIf(IsNull([Task32 Start Date]),Date()+30,[Task32 Start Date]) OR IIf(IsNull([Task32 Start Date]),Date()+31,[Task32 Start Date]) &lt; IIf(IsNull([Task33 Start Date]),Date()+31,[Task33 Start Date]) OR IIf(IsNull([Task33 Start Date]),Date()+32,[Task33 Start Date]) &lt; IIf(IsNull([Task34 Start Date]),Date()+32,[Task34 Start Date]) OR IIf(IsNull([Task34 Start Date]),Date()+33,[Task34 Start Date]) &lt; IIf(IsNull([Task35 Start Date]),Date()+33,[Task35 Start Date]) OR IIf(IsNull([Task35 Start Date]),Date()+34,[Task35 Start Date]) &lt; IIf(IsNull([Task36 Start Date]),Date()+34,[Task36 Start Date]))`
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>OptiOn Explicit</p>
  
  <pre><code>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"";" &amp; _
      "Extended Properties=""Excel 12.0;HDR=Yes"""
  .Open
  End With
  'tempDate=""
  For i = 0 To 34
  clauses(i) = "IIf(IsNull([Task" &amp; i + 1 &amp; " Start Date]),Date()+"&amp; i &amp;",[Task" &amp; i + 1 &amp; " Start Date]) &lt; IIf(IsNull([Task" &amp; i + 2 &amp; " Start Date]),Date()+"&amp; i &amp;",[Task" &amp; i + 2 &amp; " Start Date])"
  tempDate=tempDate &amp; "NVL([Task" &amp; i + 1 &amp; " Start Date],Date()+"&amp; i &amp;"),"
  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 (" &amp; "SELECT [Business Process ID] FROM [GEWingToWingMay25$] WHERE " &amp; Join(clauses, " OR ") &amp; ")"
  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
  </code></pre>