Solved

Access SQL VBA too Few Parameters

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now