?
Solved

Access SQL VBA too Few Parameters

Posted on 2013-11-15
4
Medium Priority
?
618 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 2000 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 52

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

830 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