Ms Access Openredcordset Reading Wrong String

General explanation: my query 2_Total returns a single value: Run the VBA function that exports the query to an excel file: Problem: OpenRecordset is not reading the correct q

Solution 1:

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 = NothingSet 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)

Solution 2:

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

SELECTSum(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.

OptionExplicitPublicFunction TRANS2()

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim xlRow AsIntegerDim dblOutput AsDoubleDim 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 = NothingSet db = NothingEndFunction