多IF语句VBA日期范围-语法错误

Question

我有VBA来运行连接到具有日期范围的表单的查询。我正在尝试编写一个IF语句，其中包含两个文本框，分别为"开始日期"one_answers"结束日期"。我在下面的IF语句中遇到语法错误，你能帮忙吗？：

Option Compare Database
Function ImportPurchases()
Dim qdfPassThrough As DAO.QueryDef
Dim MyDB As Database
Dim strConnect As String
Set db = CurrentDb()
DoCmd.SetWarnings False
Set qdfPassThrough = db.QueryDefs("Netezza_abc_purch_track")
If Not IsNull((Forms![DateRange].StartDate)) And Not IsNull((Forms![DateRange].EndDate)) Then
    strSQL = "SELECT A.STORE_NBR,A.ITEM_NBR, A.NDC_NBR, C.BUSINESS_UNIT_NBR, C.INV_CUST_NAME ,C.BUSINESS_UNIT_NAME, Sum(SHIPPED_QTY)as Allocated_Qty, Sum(INV_LINE_AMT) as Extended_Cost FROM FCT_DLY_INVOICE_DETAIL A, FCT_DLY_INVOICE_HEADER B, DIM_INVOICE_CUSTOMER C WHERE A.INV_HDR_SK = B.INV_HDR_SK AND B.DIM_INV_CUST_SK = C.DIM_INV_CUST_SK AND A.STORE_NBR=B.STORE_NBR AND A.INV_DT BETWEEN (" & Forms![DateRange].StartDate & ")and (" & Forms![DateRange].EndDate & ")AND A.SUPPLIER_NBR NOT IN ('50000181', '20000775', '50000809', '50000950') AND A.SRC_SYS_CD = 'ABC' AND C.INV_CUST_NAME NOT LIKE '%340B%' AND C.BUSINESS_UNIT_NAME NOT LIKE '%340B%' Group by 1,2,3,4,5,6"
Else
    strSQL = "ERROR"
End If
qdfPassThrough.SQL = strSQL
strConnect = "ODBC;DSN=NZSQL;Database=PRD_EDW_RXPURCHASING_DB;Server"
qdfPassThrough.Connect = strConnect
qdfPassThrough.Close

DoCmd.SetWarnings True
End Function

Answer 1

If关键字不能在同一语句中使用两次。取下第二个。

If Not IsNull(...) And Not IsNull(...) Then
    strSQL = ...
Else
    strSQL = ...
End If

（准确的错误信息是什么？）

（我想知道为什么你的VBA缺少空格？编辑器会清理所有这些，或者弹出对话框。我想我只是好奇。）

---

截至上午9:00，您的代码版本有以下比较条款：

isnull  >= (Forms![DateRange].StartDate)

我想你想要这样的东西：

IsNull((Forms![DateRange].StartDate))

作为一个一般性的建议，我建议您使用测试或试用的心态制作一些最低限度的代码，学习构建块，然后根据您的实际需求进行工作。

重要的

在每个模块的顶部键入Option Explicit。然后使用Debug > Compile Database。这将揭示代码中的关键错误，并可能导致您解决语法错误。

Answer 2

很可能您（还）需要为日期格式化正确的字符串表达式：

... (A.INV_DT Between '" & Format(Forms![DateRange]!StartDate, "yyyy/mm/dd") & "' And '" & Format(Forms![DateRange]!EndDate, "yyyy/mm/dd") & "') And A.SUPPLIER_NBR ...

Answer 3

我最终得到了这个：

选项显式专用子命令9_Click（）'JB Function ImportPurchases（）将其更新为private sub，Function用于模块，并更新名称以反映按钮名称加_click（）将数据库Dim为DAO。数据库Dim qdf As QueryDef

Dim qdfPassThrough As DAO.QueryDef
Dim MyDB As Database
Dim strConnect As String
'JB Dim SQLstr As String, you had this reversed in the sql :)
Dim strSQL As String
Dim DateStart As Date
Dim DateEnd As Date
Dim DateStart_string As String
Dim DateEnd_string As String

    'Set db and querydef variables
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Netezza_abc_purch_track")
'Initialize variables with values from the form
      DateStart = Forms!DateRange!DateStart.Value
    DateStart_string = Format(DateStart, "yyyy-mm-dd")
    DateEnd = Forms!DateRange!DateEnd.Value
    DateEnd_string = Format(DateEnd, "yyyy-mm-dd")

    strSQL = "SELECT A.STORE_NBR,A.ITEM_NBR, A.NDC_NBR, C.BUSINESS_UNIT_NBR, C.INV_CUST_NAME ,C.BUSINESS_UNIT_NAME, Sum(SHIPPED_QTY)as Allocated_Qty, Sum(INV_LINE_AMT) as Extended_Cost " & _
    "FROM FCT_DLY_INVOICE_DETAIL A, FCT_DLY_INVOICE_HEADER B, DIM_INVOICE_CUSTOMER C WHERE A.INV_HDR_SK = B.INV_HDR_SK AND B.DIM_INV_CUST_SK = C.DIM_INV_CUST_SK AND A.STORE_NBR=B.STORE_NBR " & _
    "AND A.INV_DT BETWEEN ('" & DateStart_string & "') and ('" & DateEnd_string & "')  AND A.SUPPLIER_NBR NOT IN ('50000181', '20000775', '50000809', '50000950') " & _
    "AND A.SRC_SYS_CD = 'ABC' AND C.INV_CUST_NAME NOT LIKE '%340B%' AND C.BUSINESS_UNIT_NAME NOT LIKE '%340B%' Group by 1,2,3,4,5,6"
   qdf.SQL = strSQL
 Set db = Nothing
    Set qdf = Nothing
    'DoCmd.RunMacro "Macro1"
DoCmd.OpenQuery "Netezza_abc_purch_track"
End Sub