Solved

Access SQL VBA too Few Parameters

Posted on 2013-11-15
4
608 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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 …

740 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