Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Feb 2010
    Location
    Milwaukee, Wisconsin, USA
    Posts
    25
    Thanks
    0
    Thanked 7 Times in 2 Posts

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

    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

  2. #2
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    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.
    Last edited by mrjimphelps; 2016-09-22 at 08:31.

  3. #3
    Lounger
    Join Date
    Feb 2010
    Location
    Milwaukee, Wisconsin, USA
    Posts
    25
    Thanks
    0
    Thanked 7 Times in 2 Posts
    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.

  4. #4
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,723
    Thanks
    146
    Thanked 156 Times in 149 Posts
    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...
    Talk is cheap because supply exceeds demand

  5. #5
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    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.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  7. #7
    Lounger
    Join Date
    Feb 2010
    Location
    Milwaukee, Wisconsin, USA
    Posts
    25
    Thanks
    0
    Thanked 7 Times in 2 Posts
    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.

  8. #8
    Lounger
    Join Date
    Feb 2010
    Location
    Milwaukee, Wisconsin, USA
    Posts
    25
    Thanks
    0
    Thanked 7 Times in 2 Posts
    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.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,723
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Quote Originally Posted by kc27 View Post
    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.
    Talk is cheap because supply exceeds demand

  11. #11
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,723
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Quote Originally Posted by MarkLiquorman View Post
    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!
    Talk is cheap because supply exceeds demand

  12. #12
    Lounger
    Join Date
    Feb 2010
    Location
    Milwaukee, Wisconsin, USA
    Posts
    25
    Thanks
    0
    Thanked 7 Times in 2 Posts
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •