Getting Run-time error ‘3061’ Too few parameters. Expected 1.

Topic

New Reply

I am using Access 2013. When I run a macro, I get this error

Run-time error ‘3061’
Too few parameters. Expected 1.

If I open the debug window, I see the code below. The line in bold is highlighted by the debugger.

Can anyone give me some guidance on how to troubleshoot this? To make thinks even more fun, I am new to Access, or any database application, am not a developer, and did not write the macro that is failing. But I do need get the macro working again, so any ideas that you may have on how to resolve this would be appreciated. Thanks in advance for any assistance on this problem.

Option Compare Database

Public Sub GetMISLManual()
dbdFields = Array(“P0210”, “P0310”, “P0410”, “P0510”, “P0610”, “P0710”, “P0810”, “P0910”, “P1010”, “P1110”)
SQLFields = Array(“FEB”, “MAR”, “APR”, “MAY”, “JUN”, “JUL”, “AUG”, “SEP”, “OCT”, “NOV”, “DEC”, “JAN”)
‘dbdFields = Array(“P1210”)
‘SQLFields = Array(“DEC”)
‘NETSALES=8, EOM=17
‘SQLtable1 = “dbo.ACTLY_CPH” ‘Actual=”dbo.ACTTY_CPH”
For n = 0 To UBound(dbdFields)
UpdateRPCommTables dbdFields(n), SQLFields(n), “SalesF”, 8, “[dbo.ACTTY_CPH]”
Next n
For n = 0 To UBound(dbdFields)
UpdateRPCommTables dbdFields(n), SQLFields(n), “StockF”, 17, “[dbo.ACTTY_CPH]”
Next n
End Sub

Public Sub GetMISLAuto()
currEOPdate = EOMDate_Curr(Date – 3)
dbField = GetMonthSpan(currEOPdate, currEOPdate)
SQLField = UCase(Format(Mid(dbField, 2, 2) & “/” & Mid(dbField, 4, 2), “mmm”))
UpdateRPCommTables dbField, SQLField, “SalesF”, 8, “[dbo.ACTTY_CPH]”
UpdateRPCommTables dbField, SQLField, “StockF”, 17, “[dbo.ACTTY_CPH]”
End Sub

Public Sub UpdateRPCommTables(ByVal dbField As String, ByVal sField As String, rTable As String, ele As String, ByVal stable As String)
Dim DBSS As DAO.Database
Dim DBR As DAO.Database
Dim RSD As DAO.Recordset, RSS As DAO.Recordset
Set DBR = OpenDatabase(DB_RPCOMM)
Set RSD = DBR.OpenRecordset(“SELECT Dept, ” & dbField & ” FROM ” & rTable & ” ORDER BY Dept”, dbOpenDynaset)
Set DBSS = OpenDatabase(“SQLPROD_BT.dsn”, dbDriverNoPrompt, True, “ODBC; DRIVER={SQL SERVER};SERVER=mmm.sqldb.prod.bonton.com;DATABASE=production”)
‘get record keys
sql1 = “SELECT DEPT_CODE, SUM(” & sField & “) AS sData FROM ” & stable & _
” WHERE ELE_CODE =” & ele & ” GROUP BY DEPT_CODE ORDER BY DEPT_CODE”
Set RSS = DBSS.OpenRecordset(sql1, dbOpenDynaset)
Do While Not RSS.EOF
SysCmd acSysCmdSetStatus, “Retrieving data for ” & RSS!DEPT_CODE & ” field ” & dbField
RSD.FindFirst “Dept ='” & Format(RSS!DEPT_CODE, “000”) & “‘”
If RSD.NoMatch Then
RSD.AddNew
RSD!Dept = Format(RSS!DEPT_CODE, “000”)
Else
RSD.Edit
End If
RSD.Fields(dbField) = Round(Nz(RSS!sData, 0), 0)
RSD.Update
RSS.MoveNext
Loop
RSS.Close: Set RSS = Nothing
DoEvents
RSD.Close: Set RSD = Nothing
DBSS.Close: Set dbs = Nothing
DBR.Close: Set DBR = Nothing
SysCmd acSysCmdClearStatus
End Sub

Public Function EOMDate_Curr(ByVal xDate As Date)
‘Returns the last fiscal date of the month in which ‘xDate’ resides
Dim DBI As DAO.Database
Dim RSM As DAO.Recordset
Set DBI = OpenDatabase(DB_IMG1, , True)
Set RSM = DBI.OpenRecordset(“CntlDate”, dbOpenDynaset)
RSM.FindFirst “EOMDate >= #” & xDate & “#”
If RSM.NoMatch Then
EOMDate_Curr = Date
Else
EOMDate_Curr = RSM!EOMDate
End If
RSM.Close: Set RSM = Nothing
DBI.Close: Set DBI = Nothing

End Function

Public Function GetMonthSpan(ByVal startEOMDate As Date, ByVal endEOMDate As Date)
Dim DBI As DAO.Database, RSDate As DAO.Recordset
Set DBI = OpenDatabase(DB_IMG1, , True)
Set RSDate = DBI.OpenRecordset(“SELECT * FROM CntlDate WHERE EOMDate >=#” & startEOMDate & “# AND EOMDate <=#" & endEOMDate & "#", dbOpenDynaset)
GetMonthSpan = RSDate!dbField
RSDate.MoveNext
Do Until RSDate.EOF
GetMonthSpan = GetMonthSpan & "+" & RSDate!dbField
RSDate.MoveNext
Loop
RSDate.Close: Set RSDate = Nothing
DBI.Close: Set DBI = Nothing
End Function

Reply | Quote

Replies

I think some of your double quotes are misplaced. Try this:

Set RSD = DBR.OpenRecordset(SELECT Dept & dbField FROM rTable ORDER BY Dept, dbOpenDynaset)

or this:

Set RSD = DBR.OpenRecordset(SELECT Dept, dbField FROM rTable ORDER BY Dept, dbOpenDynaset)

SELECT and ORDER BY are commands, not values, so they would never be in quotes or be part of a quote. Neither would you SELECT something in quotes, because you never enclose a data field name in quotes. The only thing you ever put in quotes is some text you want to display or capture.

I don’t know Access, but I do know SQL; and this looks like SQL.

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

Thanks for the suggestion. I did as you suggested, but neither code worked. The word SELECT would be highlighted, and I would get a Microsoft Visual Basic for Application error message that stated:

Compile error
Expected: expression.

Reply | Quote

Helps if I correct the right code, sorry

Eliminate spare time: start programming PowerShell

Reply | Quote

I looked at this and think that you do need the quotes.

I found an example as below:
Set rest = dbs.OpenRecordset(“SELECT * FROM Customers WHERE LastVisitDate BETWEEN Date()-60 ” & “AND Date()-30 ORDER BY LastVisitDate DESC”)

so try

Set RSD = DBR.OpenRecordset(“SELECT Dept dbField FROM rTable ORDER BY Dept”, dbOpenDynaset)
and see if it works.

I’ve always found doing quotes in VBA a bit of a minefield…

Eliminate spare time: start programming PowerShell

Reply | Quote

I’m guessing that my suggestion doesn’t work because the Access query language is a little different than standard SQL.

One of the things I was looking for in your code was a function or subroutine called “OpenRecordset” or “DBR.OpenRecordset”, or whatever the name is. The error message indicates that no parameters were listed on the line that that function was called, yet the function requires one parameter to be listed.

For example, if you had a function called ABS, and it returned the absolute value of a number, it would have only one parameter — the original number. In this example, you might have x = ABS(y), with y equaling -5. x would then equal 5. It wouldn’t make sense to put x = ABS(), or x = ABS(5,2), because you are looking for the absolute value of a single number. If you have x = ABS(), what would x equal? Access doesn’t know, because you have too few parameters. So somewhere buried in your macro is something like that.

Sometimes these queries can get complicated, when you have functions inside of functions. It makes troubleshooting complicated.

Group "L" (Linux Mint)
with Windows 10 running in a remote session on my file server

Reply | Quote

I made the change access-mdb suggested, and ran the macro. It stopped at that line of code and gave me the error below:

Run-Time error ‘3075’:
Syntax error (missing operator) in query expression ‘Dept dbField’.

Thanks mrjimphelps for the thoughtful reply. I am not the author of the code, nor a programmer, and until a couple of weeks ago not even an Access user, so this is all new to me. That, and I am not familiar with the data at all. So I do appreciate the help, I understand what you are saying, but without the Access skills and knowledge of the data, I cannot utilize your advice fully, or even fractionally. I guess I was hoping this was a simple syntax issue.

Reply | Quote

I made the change access-mdb suggested, and ran the macro. It stopped at that line of code and gave me the error below:

Run-Time error ‘3075’:
Syntax error (missing operator) in query expression ‘Dept dbField’.

Thanks mrjimphelps for the thoughtful reply. I am not the author of the code, nor a programmer, and until a couple of weeks ago not even an Access user, so this is all new to me. That, and I am not familiar with the data at all. So I do appreciate the help, I understand what you are saying, but without the Access skills and knowledge of the data, I cannot utilize your advice fully, or even fractionally. I guess I was hoping this was a simple syntax issue.

My bad, I think there should be a comma between Debt and dbField,. However if Wendell and Mark think it’s difficult then it’s way above my pay grade.

It’s a bit like writing a book; the grammar might well be perfect, but it might not make any sense to anyone.

Eliminate spare time: start programming PowerShell

Reply | Quote

WendellB – yes, if you read post #7, you will see your assessment of my height compared to the depth of this issue is pretty darn accurate.

Reply | Quote

What that VBA code is doing is creating a SQL SELECT statement, and Access allows you to do that in the OpenRecordset command. However it is very difficult to debug that way. A better approach is to use a string expression to create the SQL statement prior to the OpenRecordset command so you can inspect it, and actually test it to see if it works.

Unfortunately, you appear to be in way over your head. I would be beating the bushes looking for the original person who designed that, and they aren’t available, looking to hire an Access developer who is very proficient in DAO and VBA. There are so many variables in what could be causing the problem that IMHO you need an expert.

Reply | Quote

In the future, it is often prudent to have an intermediate line of code like this:

strSQL =”SELECT Dept, ” & dbField & ” FROM ” & rTable & ” ORDER BY Dept”
Set RSD = DBR.OpenRecordset(strSQL, dbOpenDynaset)

This way when that “SET RSD =…” line errors, you can easily look at contents of strSQL and see what it contains. The more complicated the formulation, the more likely you’ll make an error. Let me rephrase that, the more complicated the formulation, the more likely that I’ll make an error!

Reply | Quote

The more complicated the formulation, the more likely you’ll make an error. Let me rephrase that, the more complicated the formulation, the more likely that I’ll make an error!

Mark, been there, done that , wearing the T-shirt!

Eliminate spare time: start programming PowerShell

Reply | Quote

I just wanted to follow up on this. I did get some help with the kind of troubleshooting that WendellB suggested. Their theory was that a Field name in tbe SQL was incorrect. An internet search also returned this idea for Run-time error ‘3061’. Too few parameters. Expected 1: “…this happens when the field name(s) in your sql query do not match the table field name(s), i.e. a field name in the query is wrong or perhaps the table is missing the field altogether.” That turned out to be my issue. Once I added the suggested troubleshooting code. the Access intermediate window showed this message: SELECT Dept, P0917 FROM SalesF ORDER by Dept

I checked SalesF table, saw P0917 was not there, added it and that resolved the problem. The instructions I was given are below. The concept could probably be adapted to troubleshoot similar problems, so I am posting it below Still not a substitute for having an Access developer on site on or on call. Hope that gets fixed soon.

Prior to the statement that is highlighted (the line of code that I bolded in my first post above), insert this:

Dim sSQL As String
sSQL = ”
Debug.Print sSQL

…and then COPY from OpenRecordset: “Select Dept,” to “Order by Depy” and PASTE after sSQL =

Then REPLACE the part you COPIED in the OpenRecordset with sSQL.

You end up with is a new variable, sSQL, that contains the SQL statement that 1) will be printed in the Immediate Window and 2) will be executed by your OpenRecordset statement. So when the code errors, you will have the exact SQL that it choked on.

Reply | Quote