MS Access OpenRecordset and too few parameters issue

Question

This is a follow-up question of MS Access OpenRedcordset reading wrong string. I'm certain that I have enough parameters, fields declaration to pass values to OpenRecordSet method but I am still stuck.

General explanation:

Users input start date and end date on a form that filters my query 2_Total (single value) My query
Run the VBA function that exports the query to an excel file

Problem: Too few parameters. Expected 4 error on Set rst = qry.OpenRecordset(dbOpenDynaset)

SQL Design View

PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
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")

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

    Set rst = qry.OpenRecordset(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

The following didn't solve the issue. I still have Too few parameters. Expected 4 error.

qry.Parameters("BeginDate").Value = [Forms]![Run]![textBeginOrderDate]
qry.Parameters("EndDate").Value = [Forms]![Run]![textendorderdate]

Possible duplicate of [Is it possible to pass parameters programmatically in a Microsoft Access update query?](https://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update) — iDevlop, Oct 30 '17 at 14:40

iDevlop · Accepted Answer · 2017-10-30T15:53:13.953

0

try the syntax: qry.Parameters("BeginDate").Value = Forms![Run]![textBeginOrderDate]

You can also add a line with debug.print Forms![Run]![textBeginOrderDate] to make sure it is the value you expect.

https://stackoverflow.com/a/24535025/78522

Another possibility is to modify the query and use for example
[Forms]![Run]![textBeginOrderDate] as a criteria

edited Oct 30 '17 at 15:53

answered Oct 30 '17 at 14:38

iDevlop

24,841
11
90
149

thank you so much for your response. but I still get the same error, too few arameters. expected 4. – J Bowman Oct 30 '17 at 14:45

score 0 · Answer 2 · answered Oct 30 '17 at 16:07

0

Set Where clause to BeginDate and EndDate in SQL :

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];

answered Oct 30 '17 at 16:07

J Bowman

25
9

In answering your last question that you linked here, [my solution](https://stackoverflow.com/a/46983213/1422451) towards bottom used this exact SQL statement. In post above, you declare the params but never used them. Hence the error. The idea is to bind the form control values to parameters **outside** the SQL in VBA. – Parfait Oct 30 '17 at 16:17

score 0 · Answer 3 · answered Oct 30 '17 at 18:49

Try this:

PARAMETERS 
    [Forms]![RUN]![textBeginOrderDate] DateTime, 
    [Forms]![RUN]![textendorderdate] DateTime;
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]));

score 0 · Answer 4 · answered Oct 30 '17 at 20:24

try this SQL in your query:

    SELECT 
      Sum(dbo_so_salesHistory.DollarsSold) AS SumOforderamt,
      Min(dbo_so_salesHistory.InvoiceDate) AS MinOforderdate,
      Max(dbo_so_salesHistory.InvoiceDate) AS MaxOforderdate,
      Count(dbo_so_salesHistory.InvoiceDate) AS CountOforderdate, 
      forms!run![textBeginOrderDate] AS tbegin, 
      forms!run![textendorderdate] AS tend
    FROM dbo_so_salesHistory
    WHERE 
      (((dbo_so_salesHistory.InvoiceDate) Between 
      [forms]![run]![textBeginOrderDate] And 
      [forms]![run]![textendorderdate]))
    GROUP BY 
      forms!run![textBeginOrderDate], 
      forms!run![textendorderdate];

and this VBA code:

Private Sub cmdRun_Click()
    Dim db As DAO.Database
    Dim qry As QueryDef
    Dim rst As Recordset

    Set db = CurrentDb

    Set qry = db.QueryDefs("2_Total")
    qry.Parameters(0).Value = Forms!Run![textBeginOrderDate]
    qry.Parameters(1).Value = Forms!Run![textendorderdate]

    Set rst = qry.OpenRecordset(dbOpenDynaset)

End Sub

MS Access OpenRecordset and too few parameters issue

4 Answers4