Run-time error '3061'. Too few parameters. Expected 3

Question

I am trying to use VBA to pull data from access into excel. I am able to use this script for other query's, but when for this specific query, I get this parameter error. The break happens here:

Set rs = A.CurrentDb().QueryDefs("Query1").OpenRecordset()

Sub test()

Dim ws As Worksheet
Dim A As Object
Dim rs As Object

Application.DisplayAlerts = False

Set A = CreateObject("Access.Application")
Set ws = ThisWorkbook.Sheets("Sheet1")

A.Visible = True
A.OpenCurrentDatabase ("Y:\UW\B\201811.accdb")
A.DoCmd.OpenQuery ("Query1")

Set rs = A.CurrentDb().QueryDefs("Query1").OpenRecordset()

If Not rs.EOF Then
ws.Range("A2").CopyFromRecordset rs
End If

For iCols = 0 To rs.Fields.Count - 1
Worksheets("Sheet1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
rs.Close

 Application.DisplayAlerts = True

End Sub

Here is query1:

SELECT W.MED, W.RX, W.HSAHRA, W.ACR, W.ML, Sum(W.MBRS) AS             
SumOfMBRS, Sum(W.PREM) AS SumOfPREM, Sum(W.CLMSTAT) AS SumOfCLMSTAT
FROM W
WHERE (((W.[DATE]) Between [Enter Start Date:] And [Enter End Date:]) AND 
((W.[ST])=[Enter State:]))
GROUP BY W.MED, W.RX, W.HSAHRA, W.ACR, W.ML
ORDER BY W.MED, W.RX, W.HSAHRA, W.ACR, W.ML;

Also, how do i convert query1 into a sqlstring?

What kind of query is Query1? Can you use the edit link below the question to add the SQL behind the query? Or, if you use this query in Access, itself, does it prompt for information? — Cindy Meister

June7 June7 · Accepted Answer · 2018-12-24T23:06:29

User should input criteria into controls on form then reference those controls as parameter inputs:

strSQL = "SELECT MED, RX, HSAHRA, ACR, ML, Sum(MBRS) AS SumOfMBRS, Sum(PREM) AS SumOfPREM, Sum(CLMSTAT) AS SumOfCLMSTAT
FROM W " & _
"WHERE [DATE] Between #" & Me.tbxStart & "# And #" & Me.tbxEnd & "# AND [ST]='" & Me.tbxState & "' " & _
"GROUP BY MED, RX, HSAHRA, ACR, ML " & _
"ORDER BY MED, RX, HSAHRA, ACR, ML;"

Or in your case, reference cells of worksheet. If you want to use popups for user input, will have to use InputBox() function.

Run-time error '3061'. Too few parameters. Expected 3

1 Answers