Results 1 to 9 of 9
  1. #1
    abenitez77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3

    Question Running Query gives me error 3061 (Too few parameters. Expected 2)

    I can't pinpoint where I am getting this error (Too Few parameters. Expected 2) on my code. Can you please help me find the needle in the haystack? I am declaring



    Code:
    Dim loRS as Object
    
    SELECT R1.AuditID, Left(NZ(R1.TranCode,""), 3) AS ClientCode, 
        Max(R1.TranCodeText) AS MaxOfTranCodeText, 
        Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) IN ("-1","TRUE"), R1.NetAmt + abs(R1.[PaybackAmt]), 0 )) AS GrossDeducted, 
        Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) IN ("-1","TRUE"), R1.PaybackAmt, 0 )) AS PayBacks, 
        Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) IN ("-1","TRUE"), R1.[NetAmt], 0 )) AS NetDeducted, 
        Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) NOT IN ("-1" ,"TRUE")   
            AND UCase(NZ(R1.User004_Uploaded,"")) IN ("-1","TRUE"), R1.[NetAmt], 0 
                )
            ) AS Uploaded, 
        Sum(IIF(UCase(NZ(R1.User003_MailDate,"")) NOT IN ("")   
                AND UCase(NZ(R1.User004_Deducted,"")) IN ("","0","FALSE")   
                AND UCase(NZ(R1.User019_Deducted,"")) IN ("","0","FALSE")   , R1.NetAmt, 0 
                )
            ) AS Mailed, 
        SUM(IIF(ISNULL(R1.AuditId)= True, 1, 0 ) ) AS Cnt 
    FROM (RptCPClaimsRollupRACReportDetail R1 
    Left Join RptCPClaimsRollupRACReport_ClaimCodeDesc R2 
        ON R1.ClientCode = R2.ClaimCode) 
    Left Join ScrCpClaimsData SCD 
        ON R1.AuditID = SCD.AuditID AND R1.ClaimNum = SCD.ClaimNum 
    WHERE R1.AuditID IN( 6133,6134,6183 )  
        AND ( IIF(ISNULL(R1.user003_MailDate)= True, Cdate("01/01/1900"), Cdate(R1.user003_MailDate)) < CDate("01/21/2022")  
            AND IIF(ISNULL(R1.LastTranDate)= True, Cdate("01/01/1900"), R1.LastTranDate) < CDate("01/21/2022")  
            AND IIF(ISNULL(SCD.UploadDate)= True, Cdate("01/01/1900"), SCD.UploadDate) < CDate("01/21/2022")  
            AND IIF(ISNULL(SCD.LastDedDate)= True, Cdate("01/01/1900"), SCD.LastDedDate) < CDate("01/21/2022") 
            )  
    GROUP BY R1.AuditID, Left(NZ(R1.TranCode,""), 3) 
    
    Set loRS = CurrentDb.OpenRecordset(xSqlMain, dbReadOnly, dbReadOnly)


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I applaud you for using code tags! Error number AND message is great too.
    Next would be to identify the line that causes the error with a comment in the code like '<< error happens here, such & such text is highlighted. Or try formatting the part bold red.

    The sql side of Access can resolve parameters much easier than the vba side, and there are at least 3 ways of getting around this issue.
    Most common one is to declare parameters and assign them (often by referencing form fields/controls). I found that an easier method is to declare variables and assign them values by referencing the form fields, then use the variables in the vba sql. I would have provided an example from your sql but I have no idea which part is raising the error.
    BTW - odd to see "-1" or "True" as values for the IN clause. -1 is the numeric equivalent of True, which is a Boolean data type. "-1" and "True" is strictly text so watch out for unexpected results if your values are not in a text field.

    EDIT - seems you have repeated the RecordsetOptionEnum parameter? -dbReadOnly, dbReadOnly
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Your code is not complete as posted, so would be hard for an outsider to pinpoint the source, most likely some misspelled field names... I think you are adding a lot of overhead by using all those type conversion strings (UCase, CDate) to get your values to match what your target data types are ("-1","True"), why not simply use the actual values and wrap them in the proper character (single quote for string, none for numbers, # for dates)?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Suspicious:

    Code:
        SUM(IIF(ISNULL(R1.AuditId)= True, 1, 0 ) ) AS Cnt 
    FROM (RptCPClaimsRollupRACReportDetail R1 
    Left Join RptCPClaimsRollupRACReport_ClaimCodeDesc R2 
        ON R1.ClientCode = R2.ClaimCode) 
    Left Join ScrCpClaimsData SCD 
        ON R1.AuditID = SCD.AuditID AND R1.ClaimNum = SCD.ClaimNum 
    WHERE R1.AuditID IN( 6133,6134,6183 )

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    This happens a lot when you have parameters in the sql and use a recordset with that sql?
    Code:
    Set loRS = CurrentDb.OpenRecordset(xSqlMain, dbReadOnly, dbReadOnly)
    The you use something like http://www.accessmvp.com/thedbguy/co...?title=generic
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    abenitez77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3
    Why suspicious? because of the different names? I can see why that would raise an eyebrow but they really have the same data and it is correct.
    Quote Originally Posted by davegri View Post
    Suspicious:

    Code:
        SUM(IIF(ISNULL(R1.AuditId)= True, 1, 0 ) ) AS Cnt 
    FROM (RptCPClaimsRollupRACReportDetail R1 
    Left Join RptCPClaimsRollupRACReport_ClaimCodeDesc R2 
        ON R1.ClientCode = R2.ClaimCode) 
    Left Join ScrCpClaimsData SCD 
        ON R1.AuditID = SCD.AuditID AND R1.ClaimNum = SCD.ClaimNum 
    WHERE R1.AuditID IN( 6133,6134,6183 )

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have Option Explicit as second line in the module?
    3061 is often a spelling issue.
    Helpful if you post the entire code.

  8. #8
    abenitez77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3
    Great Functions. This helped me find my issue. 2 fields that were not in the table or misspelled.
    Quote Originally Posted by Welshgasman View Post
    This happens a lot when you have parameters in the sql and use a recordset with that sql?
    Code:
    Set loRS = CurrentDb.OpenRecordset(xSqlMain, dbReadOnly, dbReadOnly)
    The you use something like http://www.accessmvp.com/thedbguy/co...?title=generic

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 8 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with error 3061 too few parameters. Expected 1
    By NightWalker in forum Queries
    Replies: 6
    Last Post: 05-04-2020, 04:28 PM
  2. Run-Time Error '3061: Too few parameters. Expected 2.
    By Rickochezz in forum Import/Export Data
    Replies: 1
    Last Post: 11-01-2016, 07:29 AM
  3. Error 3061. Too few parameters. Expected 1.
    By Glenn_Suggs in forum Programming
    Replies: 5
    Last Post: 02-03-2015, 12:03 PM
  4. Replies: 3
    Last Post: 04-26-2013, 01:37 PM
  5. 3061 Error. Too few parameters. Expected 1.
    By rghollenbeck in forum Queries
    Replies: 5
    Last Post: 09-28-2011, 12:12 PM

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
  •  
Other Forums: Microsoft Office Forums