vba - 将sql查询的结果存储到变量 Access vba中

Question

我有一个 SQL 查询，它根据三个条件返回一些字段。

1. LOC 字段有一个字符串“Alipore”
2. Date 大于并等于变量 sdate，该变量从表单上的文本框获取值。
3. 日期小于并等于从表单上的文本框获取值的变量 edate。

但我收到错误“两个少数参数 2”错误。

Sub Test()
    Dim rs As DAO.Recordset
    Dim sqlMax As String
    Dim result As Integer
    Dim startd, endd As String

    startd = Me.txtsdate.Value

    endd = Me.txtedate.Value

    sqlMax = "SELECT Sum(Salesdata.FOOD) AS SumOfFOOD, Sum(Salesdata.LIQUORS) AS SumOfLIQUORS, Sum(Salesdata.SMARTPORTION) AS SumOfSMARTPORTION, Sum(Salesdata.[SP TAKEAWAY]) AS [SumOfSP TAKEAWAY]," _
     & "Sum(Salesdata.TAKEAWAY) AS SumOfTAKEAWAY, Sum(Salesdata.TAX_KKCESS02) AS SumOfTAX_KKCESS02, Sum(Salesdata.TAX_SBC020) AS SumOfTAX_SBC020, Sum(Salesdata.TAX_SERVICECHARGE) AS SumOfTAX_SERVICECHARGE," _
     & "Sum(Salesdata.TAX_VAT145) AS SumOfTAX_VAT145, Sum(Salesdata.AMEX) AS SumOfAMEX, Sum(Salesdata.CASH) AS SumOfCASH, Sum(Salesdata.MASTERCARD) AS SumOfMASTERCARD, Sum(Salesdata.VISA) AS SumOfVISA, Sum(Salesdata.OTHERS) AS SumOfOTHERS," _
     & "Sum(Salesdata.Vcloud) AS SumOfVcloud, Sum(Salesdata.MANAGERAC) AS SumOfMANAGERAC FROM Salesdata" _
     & "WHERE (((Salesdata.Loc) = 'Alipore'))" _
     & "HAVING (((Salesdata.DATE)>=" & startd & " And (Salesdata.DATE)<=" & endd & "));"

设置 rs = CurrentDb.OpenRecordset(sqlMax, dbOpenDynaset)

    Set rs = CurrentDb.OpenRecordset(sqlMax)

    result = rs.Fields(0)

    MsgBox result

    Set rs = Nothing

    rs.Close
End Sub

Answer 1

天哪，我找到了，太简单了。前面需要一个空格，这就是它在 access 中运行的原因，但是当我将其复制到 VBA 并重构 sql 语句时。

应该是

& "WHERE (((Salesdata.Loc) = 'Alipore'))"_

而不是

&“WHERE (((Salesdata.Loc) = 'Alipore'))” _