Solved

Access SQL VBA too Few Parameters

Posted on 2013-11-15
4
610 Views
Last Modified: 2013-11-18
Trying to move a query from an object into VBA code.  Getting "Too few parameters error when setting the sql to a recordset.  Here's the code;

Dim SQL1
Dim SQL2
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

    Set db = CurrentDb


    SQL1 = "SELECT qselLawsonHrsAndPay.EmpID, Sum(qselLawsonHrsAndPay.GrossPay) AS TotalWages " _
            & "FROM qselLawsonHrsAndPay " _
            & "WHERE (((qselLawsonHrsAndPay.Class) = '401') And ((qselLawsonHrsAndPay.PayYr) = Year(Date)) And ((qselLawsonHrsAndPay.WAGES_FLAG) = 'I')) " _
            & "GROUP BY qselLawsonHrsAndPay.EmpID"

   
    Set rs1 = db.OpenRecordset(SQL1, dbOpenDynaset)
    Do Until rs1.EOF
   
    Loop
0
Comment
Question by:jrogersok
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 500 total points
ID: 39651667
I assume the class and WAGES_FLAG fields are text fields since you're wrapping the values in apostrophes. Also, I think your problem is here
(qselLawsonHrsAndPay.PayYr) = Year(Date))

Open in new window

Try this instead
SQL1 = "SELECT qselLawsonHrsAndPay.EmpID, Sum(qselLawsonHrsAndPay.GrossPay) AS TotalWages " _
        & "FROM qselLawsonHrsAndPay " _
        & "WHERE (((qselLawsonHrsAndPay.Class) = '401') And ((qselLawsonHrsAndPay.PayYr) = " & Year(Date)) & " And ((qselLawsonHrsAndPay.WAGES_FLAG) = 'I')) " _
        & "GROUP BY qselLawsonHrsAndPay.EmpID"


Set rs1 = db.OpenRecordset(SQL1, dbOpenDynaset)
Do Until rs1.EOF

Open in new window

This is assuming PayYr is a number field.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39653133
It thinks Date is a field, thus:

          & "WHERE (((qselLawsonHrsAndPay.Class) = '401') And ((qselLawsonHrsAndPay.PayYr) = Year(Date())) And ((qselLawsonHrsAndPay.WAGES_FLAG) = 'I')) " _

/gustav
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39654451
Hi,

"Year(Date())" can also be used directly in SQL so there is no need to assemble that in VBA.
But the prefix "qsel" let me guess that you are working with a query instead of a table and so the reason for this error don't need to be a problem in your current SQL string but instead of in the query you are using. If this one is also using further queries (and so on..) then you need to inspect them all to find out if any query needs a parameter.
Sometimes this error occurs also if you have too many or bad nested queries in the used query.

Cheers,

Christian
0
 
LVL 2

Author Closing Comment

by:jrogersok
ID: 39657478
It was tye Year function that was throwing it off.  Thanks for the help.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question