MS Access OpenRedcordset reading wrong string

Question

General explanation:

1. my query 2_Total returns a single value:

   

2. Run the VBA function that exports the query to an excel file:

   

Problem: OpenRecordset is not reading the correct query. My query should be exported to my excel file. Yet My VBA is pointing at wrong stuff.

My guess is that the line qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]" is not providing sufficient information?

SQL

SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) 
BETWEEN [Forms]![RUN]![textBeginOrderDate] AND [Forms]![RUN]![textendorderdate]));

VBA

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 db As DAO.Database
    Dim qry As QueryDef
    Dim rst As Recordset
    Dim prm As DAO.Parameter
    Dim strSQL As String

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

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

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

    qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]"

    Set rst = db.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

I may have deleted too much, but if "2_Totals" SQL code is precisely:

SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) 
BETWEEN [Forms]![RUN]![textBeginOrderDate] AND [Forms]![RUN]![textendorderdate]));

Then the following code will lookup that value and place it in the referenced cell in Excel. There was a loop, that didn't seem to make sense since the query returns a single value so I removed it. I don't understand why you find the last row but then add 11 rows to it, but if it changes the wrong cell, comment out the xlRow = xlRow + 11 line.

Option Explicit
Public Function TRANS2()

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim xlRow As Integer
    Dim dblOutput As Double
    Dim db As DAO.Database

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

    xlRow = (xlWS.Columns("K").End(xlDown).Row)

    dblOutput = DLookup("[SumOfDollarsSold]", "2_Totals")

    xlRow = xlRow + 11

    xlWS.Cells(xlRow, 11).Value = dblOutput

rq_Exit:
    Set xlWS = Nothing
    xlWB.Close acSaveYes
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Set db = Nothing
End Function

Answer 2

Consider releasing the querydef before OpenRecordset as the recordset call uses the last saved instance of query and not the changes to the SQL since you never official save it:

qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]"
Set qry = Nothing

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

Or better yet, open the recordset directly from querydef, using QueryDef.OpenRecordset:

qry.SQL = "SELECT * FROM [dbo_SO_SalesHistory]"

Set rst = qry.OpenRecordset(dbOpenDynaset)

---

But as @ThomasG comments, carefully check your code and integrate above suggestion accordingly. This SELECT statement does not seem to output a one-row, one-column resultset. However your posted SQL does return one row/one-column aggregate. I suspect you intended:

strSQL = "SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold" _
          & " FROM dbo_SO_SalesHistory" _
          & " WHERE [dbo_SO_SalesHistory].[InvoiceDate] BETWEEN #" _
          & [Forms]![Run]![textBeginOrderDate] & "# AND #" _
          & [Forms]![Run]![textendorderdate] & "#"

qry.SQL = strSQL

However, I suggest using parameterization for cleaner, safer, maintainable code.

strSQL = "PARAMETERS [BeginDate] Datetime, [EndDate] Datetime;" _
          & " SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold" _
          & " FROM dbo_SO_SalesHistory" _
          & " WHERE [dbo_SO_SalesHistory].[InvoiceDate]" _
          & " BETWEEN [BeginDate] AND [EndDate];"

qry.SQL = strSQL

qry![BeginDate] = [Forms]![Run]![textBeginOrderDate]
qry![EndDate] = [Forms]![Run]![textendorderdate]

Set rst = qry.OpenRecordset(dbOpenDynaset)

And yes, above VBA string can and should be saved on its own as an Access query object. So no need to re-write SQL each time. Just bind different dynamic params each time!

Set qry = db.QueryDefs("2_Total")     ' ABOVE STRING SQL STATEMENT WITH PARAMETERS

qry![BeginDate] = [Forms]![Run]![textBeginOrderDate]
qry![EndDate] = [Forms]![Run]![textendorderdate]

Set rst = qry.OpenRecordset(dbOpenDynaset)