Run Time error ‘3061’ Too few parameters, Expected 3

In manual Tests this query achieves the results I require. However I need to run it multiple times to fill a table depending on the value of DayCount, and each time I increment DayCount the Startdate must be the original StartDate + (DayCount-1).

The original SQL Statement is as follows:

INSERT INTO [Days Bookings] ( ReCustPersFirst, ReCustPersLast, ReCustName, DiId, Rooms, Canceled, Sleepers, [Value], [Avg], StartDate, DayCount )
SELECT [Net Guest Type Analysis].ReCustPersFirst, [Net Guest Type Analysis].ReCustPersLast, [Net Guest Type Analysis].ReCustName, [Net Guest Type Analysis].DiId, [Net Guest Type Analysis]![Rooms]/[Net Guest Type Analysis]![DayCount] AS Rooms, [Net Guest Type Analysis]![RCanceled]/[Net Guest Type Analysis]![DayCount] AS Canceled, [Net Guest Type Analysis]![Sleepers]/[Net Guest Type Analysis]![DayCount] AS Sleepers, Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![DayCount],2) AS [Value], Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![Rooms]/[Net Guest Type Analysis]![DayCount],2) AS [Avg], [Net Guest Type Analysis]![StartDate]+5 AS StartDate, [Net Guest Type Analysis].DayCount
FROM [Net Guest Type Analysis]
WHERE ((([Net Guest Type Analysis].DayCount) Between 6 And 6));

I have tried to put this into the Access VB just using the code as is, so as to ensure the SQL works before I apply the code for the variables. This is how I have coded it:

strsql = blank
        strsql = "INSERT INTO [Days Bookings] (ReCustPersFirst, ReCustPersLast, ReCustName, DiId, Rooms, Canceled, Sleepers, [Value], [Avg], StartDate, DayCount)"
        strsql = strsql & " SELECT [Net Guest Type Analysis]!ReCustPersFirst, [Net Guest Type Analysis]!ReCustPersLast, [Net Guest Type Analysis]!ReCustName,"
        strsql = strsql & " [Net Guest Type Analysis].DiId, [Net Guest Type Analysis].[Rooms]/[Net Guest Type Analysis].[DayCount] AS Rooms,"
        strsql = strsql & " [Net Guest Type Analysis]![RCanceled]/[Net Guest Type Analysis]![DayCount] AS Canceled, [Net Guest Type Analysis]![Sleepers]/[Net Guest Type Analysis]![DayCount] AS Sleepers,"
        strsql = strsql & " Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![DayCount],2) AS [Value],"
        strsql = strsql & " Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![Rooms]/[Net Guest Type Analysis]![DayCount],2) AS [Avg],"
        strsql = strsql & "[Net Guest Type Analysis]![StartDate]+1 AS StartDate, [Net Guest Type Analysis]!DayCount"
        strsql = strsql & " From [Net Guest Type Analysis] "
       strsql = strsql & "WHERE (([Net Guest Type Analysis]!DayCount) Between 2 And 6);"
       
        dbs.Execute strsql

But no matter how I change the code I get the following error:

Run Time error ‘3061’
Too few parameters, Expected 3.

Any Help would be appreciated
emcnamaraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe OvermanEngineerCommented:
I don't see any problems with the syntax.  You might be inserting the wrong data type into the [Days Bookings] table, double check that first.  You might try this syntax below, it might be easier to read.

strsql = "INSERT INTO [Days Bookings] (ReCustPersFirst, ReCustPersLast, ReCustName, " & _
                "DiId, Rooms, Canceled, Sleepers, [Value], [Avg], StartDate, DayCount) " & _
            "SELECT [Net Guest Type Analysis]!ReCustPersFirst, " & _
                "[Net Guest Type Analysis]!ReCustPersLast, " & _
                "[Net Guest Type Analysis]!ReCustName, " & _
                "[Net Guest Type Analysis].DiId, " & _
                "[Net Guest Type Analysis].[Rooms]/[Net Guest Type Analysis].[DayCount] AS Rooms, " & _
                "[Net Guest Type Analysis]![RCanceled]/[Net Guest Type Analysis]![DayCount] AS Canceled, " & _
                "[Net Guest Type Analysis]![Sleepers]/[Net Guest Type Analysis]![DayCount] AS Sleepers, " & _
                "Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![DayCount],2) AS [Value], " & _
                "Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![Rooms]/[Net Guest Type Analysis]![DayCount],2) AS [Avg], " & _
                "[Net Guest Type Analysis]![StartDate]+1 AS StartDate, " & _
                "[Net Guest Type Analysis]!DayCount " & _
            "From [Net Guest Type Analysis] " & _
            "WHERE (([Net Guest Type Analysis]!DayCount) Between 2 And 6);"

Open in new window

0
ldunscombeCommented:
Try This>

strsql = blank
        strsql = "INSERT INTO [Days Bookings] (ReCustPersFirst, ReCustPersLast, ReCustName, DiId, Rooms, Canceled, Sleepers, [Value], [Avg], StartDate, DayCount)"
        strsql = strsql & " SELECT [Net Guest Type Analysis].ReCustPersFirst, [Net Guest Type Analysis].ReCustPersLast, [Net Guest Type Analysis].ReCustName,"
        strsql = strsql & " [Net Guest Type Analysis].DiId, [Net Guest Type Analysis]![Rooms]/[Net Guest Type Analysis]![DayCount] AS Rooms,"
        strsql = strsql & " [Net Guest Type Analysis]![RCanceled]/[Net Guest Type Analysis]![DayCount] AS Canceled, [Net Guest Type Analysis]![Sleepers]/[Net Guest Type Analysis]![DayCount] AS Sleepers,"
        strsql = strsql & " Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![DayCount],2) AS [Value],"
        strsql = strsql & " Round([Net Guest Type Analysis]![Value]/[Net Guest Type Analysis]![Rooms]/[Net Guest Type Analysis]![DayCount],2) AS [Avg],"
        strsql = strsql & "[Net Guest Type Analysis]![StartDate]+5 AS StartDate, [Net Guest Type Analysis].DayCount"
        strsql = strsql & " From [Net Guest Type Analysis] "
       strsql = strsql & "WHERE ((([Net Guest Type Analysis]!DayCount) Between 2 And 6));"
       
        dbs.Execute strsql

Leigh
0
zoom2000Commented:
you  may  need to put spaces between joined statements ,may be there's some concatenated names that leads to misspelling,
 there are  3 misspelling field names
upload the your  database (empty of course from data) , so we can identify the error where is it?
 

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Run Time error ‘3061’
Too few parameters, Expected 3.>>
  The error message indicates that there are 3 things in the SQL statement that Access can't figure out.  As the others have said, this is most often a mis-spelled field or table name.  The other thing it can be is if you refer to a control on a form or a variable.  When executing SQL statements in code, it's up to you to resolve any references and provide a value for the parameter.
 In this case, it looks more like the first.  Typically when I bump into these, I chop the statement down and then build it up until I find the error.  The other thing you can do is paste it into the SQL view of query designer, get it working, then move it back into code.  
 By doing that, you get syntax checking and can then usually spot the error.
JimD.
0
emcnamaraAuthor Commented:
I have resolved the issue by taking a different approach:
Sub FillQdata()
Dim qry As QueryDef
Dim qName As String, rs As Recordset
Dim sFrom As Date, sTo As Date, sHotel As String
Dim Val1 As Integer, Val2 As Integer, Val3 As Integer, LoopRun As Integer
DoCmd.Hourglass True
sFrom = [Forms]![Guest Type Analysis Report]![From]
sTo = [Forms]![Guest Type Analysis Report]![To]
sHotel = [Forms]![Guest Type Analysis Report]![Hotel] & ""
qName = "Add Day to Avarage Rate Per Day"
Set qry = CurrentDb.QueryDefs(qName)
Val3 = DateDiff("d", sFrom, sTo)
Val2 = 2
Val1 = 1
If Val3 = 1 Then GoTo EndQuery Else
For LoopRun = 1 To Val3 - 1
qry.Parameters(0) = Val1
qry.Parameters(1) = Val2
qry.Parameters(2) = Val3
qry.Parameters(3) = sTo
qry.Parameters(4) = sFrom
qry.Parameters(5) = sHotel
qry.Execute
Val2 = Val2 + 1
Val1 = Val1 + 1
Next LoopRun
EndQuery:
Set qry = Nothing
DoCmd.Hourglass False
End Sub
Thanks to all for your input, I now suspect that the reason that my first approach did not work was because Parameters3,4 and 5 belong to a previous queries and it seems the VB did not know what they were. I will try and figure it out again when I have time.

Again Thanks for your help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.