OpenRecordset to resolve the form reference in a query

Question

This simple problem has me stopped dead in my tracks for few days.

General explanation of my program:

1. I have an interactive access form to filter a query by date range (start Date ex) 8/1/2017 and End Date) ex 8/31/2017)
2. Then my query returns a single value: ex) 12345.23
3. Run the VBA function.

Problem: I cannot expressly supply the parameters. The following line is highlighted in red.

strSQL = strSQL & "AND [dbo_SO_SalesHistory].[InvoiceDate] Between #"_
&[Forms]![RUN]![textBeginOrderDate] & "#And#"_
&[Forms]![RUN]![textendorderdate]&"#"

my SQL code :

SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
While (((dbo_SO_SalesHistory.InvoiceDate) Between [Forms]![RUN]![textBeginOrderDate] And [Forms]![RUN]![textendorderdate]));

full code:

Option Compare Database

Option Explicit
Public Function TRANS2()

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim acRng As Variant
    Dim xlRow As Integer

    Dim qry As QueryDef
    Dim rst As Recordset
    Dim prm As DAO.Parameter
    Dim strSQL As String

    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Users\April.CAROBAPLASTICS\Desktop\August 2017.xlsx")
    Set xlWS = xlWB.Worksheets("Totals")

    xlRow = (xlWS.Columns("K").End(xlDown).Row)
    Set qry = CurrentDb.QueryDefs("2_Total")

    strSQL = strSQL & "AND [dbo_SO_SalesHistory].[InvoiceDate] Between #"_
    & [Forms]![RUN]![textBeginOrderDate] & "# And #"_
    & [Forms]![RUN]![textendorderdate] & "#"
    qry.SQL = strSQL

    Set rst = CurrentDb.OpenRecordset("2_Total", dbOpenDynaset)

    Dim c As Integer
    c = 11   'C is the one that stores column number, in which c=1 means column A, 11 is for column K, 12 for Column L
    xlRow = xlRow + 11

     Do Until rst.EOF
        For Each acRng In rst.Fields
            xlWS.Cells(xlRow, c).Formula = acRng
            c = c + 1
        Next acRng
        xlRow = xlRow + 1
        c = 1
        rst.MoveNext
        If xlRow > 25 Then GoTo rq_Exit
    Loop


rq_Exit:
    rst.Close
    Set rst = Nothing
    Set xlWS = Nothing
    xlWB.Close acSaveYes
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function

End Function

Answer 1

You still need two spaces:

strSQL = strSQL & "AND [dbo_SO_SalesHistory].[InvoiceDate] Between #" _
& [Forms]![RUN]![textBeginOrderDate] & "# And #" _
& [Forms]![RUN]![textendorderdate] & "#"