[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access SQL VBA too Few Parameters

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Six Sigma Control Plans
Progress
Suggested Courses

649 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