Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    3061 error. too few parameters.

    I have code that works when i run the query by itself. When i convert the code to VBA i get the Error "3061 too few parameters. Expected 1." The error shows up on the OpenRecordset line.I have tried calling the qry but that gives the same error. I know there is something small that i am missing. Could someone please look at my code and explain what i am doing wrong. Thank you in advance.

    V/R
    Walker



    Code:
    Private Sub btnInProc_Click()
        'On Error GoTo EH
        Dim sSql As String
        Dim Var_Date As Date
        
        Var_Date = DMax("TestedDate", "tbl_343sTested")
        Debug.Print "<" & Var_Date & ">"
        DoCmd.Hourglass True
    '        sSql = "qry_Recall"
            sSql = "SELECT tbl_343sTested.FixturePosition, tbl_343sTested.SerialNumber, tbl_343sTested.TestedDate, tbl_343sTested.Technician, "
            sSql = sSql & " tbl_343sTested.TankTestedIn, tbl_343sTested.LoadTested, tbl_343sTested.PrePressureReading, tbl_343sTested.PrePressureUnits, tbl_343sTested.PreReading, "
            sSql = sSql & " tbl_343sTested.HighPressureReading, tbl_343sTested.HighPressureUnits, tbl_343sTested.HighReading, tbl_343sTested.PostPressureReading, "
            sSql = sSql & " tbl_343sTested.PostPressureUnits, tbl_343sTested.PostReading, tbl_343sTested.Recheck, tbl_343sTested.PassFail, tbl_343sTested.Tr343Id, "
            sSql = sSql & " tbl_343sTested.MeggerID, tbl_343sTested.PressureIndID, tbl_343sTested.Comment "
            sSql = sSql & " FROM tbl_343sTested "
            sSql = sSql & " WHERE (((tbl_343sTested.TestedDate)=[Var_Date]) AND ((tbl_343sTested.LoadTested)=DMax('LoadTested','tbl_343sTested','[tbl_343sTested.TestedDate]=#' & [Var_Date] & '#')))"
        Debug.Print sSql
        Set dbTest = CurrentDb
        Set rstTestData = dbTest.OpenRecordset(sSql)

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have to concatenate the date variable into the string, using # delimiters.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Paul,
    I am not sure where you are referring. I have some of those # at the end of the where clause. Where else should I put them? I am not very good at this sql part.

    Thank you,
    Walker

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You don't have them here:

    WHERE (((tbl_343sTested.TestedDate)=[Var_Date]) AND...

    and in neither case would you want brackets around the variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I have tried some combinations and I am not getting it to work. I am sorry I don't understand what is going on.

    WHERE ((('tbl_343sTested.TestedDate)=#' & Var_Date & '#') AND ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]=#' & Var_Date & '#')))"

    WHERE (((tbl_343sTested.TestedDate)=# & Var_Date & #) AND ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]=#' & Var_Date & '#')))"

    WHERE (((tbl_343sTested.TestedDate)=#Var_Date#) AND ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]=#' & Var_Date & '#')))"

  6. #6
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    If all those variable types are from the same source (table/query/etc), you can delete the pointer to the source... Also, I changed it for readability. I did not examine your parameter as that was distracting. I'll look after this post.
    sSql = "SELECT FixturePosition, SerialNumber, TestedDate, Technician, " _
    & "TankTestedIn, LoadTested, PrePressureReading, PrePressureUnits, PreReading, " _
    & "HighPressureReading, HighPressureUnits, HighReading, PostPressureReading, " _
    & "PostPressureUnits, PostReading, Recheck, PassFail, Tr343Id, " _
    & "MeggerID, PressureIndID, Comment " _
    & "FROM tbl_343sTested " &
    & "WHERE (((tbl_343sTested.TestedDate)=[Var_Date]) AND ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]=#' & [Var_Date] & '#')))"

  7. #7
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    I'm unsure if this is the most efficient way, but when I write an sql statement using variables within my function/sub, I use them literally without delimiters. Also, you have to understand it is a string passed to the call.
    mySQLstatement = "SELECT information " _
    & "FROM mytable " _
    & "WHERE information = " & mysubinfo & ";"
    Also, the my parameter needs to reflect the appropriate variable type. If you are comparing a string, change it to
    "WHERE information = '" & mysubinfo & "';"
    using the single quotes within the sql string value.

  8. #8
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    ' I've never used dmax. maybe this will work for the parameter statement but if not, my apologies.
    & "WHERE (TestedDate=" & [Var_Date]) & " " _
    & "AND (LoadTested=DMax('LoadTested','tbl_343sTested','[tbl_343sTested.TestedDate]=" & [Var_Date] & ");"

  9. #9
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    'Was missing the last parenthesis. And forgot to mention you were missing the semi-colon at the end of you sql statement.
    & "WHERE (TestedDate=" & [Var_Date]) & " " _

    & "AND (LoadTested=DMax('LoadTested','tbl_343sTested','[tbl_343sTested.TestedDate]=" & [Var_Date] & "));"

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    WHERE (((tbl_343sTested.TestedDate)=#" & Var_Date & "#) AND..."
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by sgthuth View Post
    And forgot to mention you were missing the semi-colon at the end of you sql statement.
    In my experience, not necessary; I never include it. You're missing the # delimiters required for a date/time field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure, but aren't you are wrapping the date variable (not in the Dmax part) with single quotes AND date delimiters?
    =#' & Var_Date & '#')
    Untested, but I think when this string starts and ends with double quotes, these segments become part of the entire concatenation, which gives you
    "...=# & Var_Date & #)..." <- note that I didn't substitute a date for var date. If I use an alias to show one way to shorten long sql, I think it should be more like:

    Code:
    sSql = "SELECT T.FixturePosition, T.SerialNumber, T.TestedDate, T.Technician, T.TankTestedIn, T.LoadTested, T.PrePressureReading, "
    sSql = sSql & "T.PrePressureUnits, T.PreReading, T.HighPressureReading, T.HighPressureUnits, T.HighReading, T.PostPressureReading, "
    sSql = sSql & " T.PostPressureUnits, T.PostReading, T.Recheck, T.PassFail, T.Tr343Id, T.MeggerID, T.PressureIndID, T.Comment "
    sSql = sSql & " FROM tbl_343sTested AS T WHERE (((T.TestedDate)= #" & [Var_Date] & "#) AND "
    sSql = sSql & "((T.LoadTested)=DMax('LoadTested','tbl_343sTested','[TestedDate]= # ' & [Var_Date] & ' #)))"
    Note: I don't think you can alias the table in an expression or function such as DMax. As for using single quotes within DMax or DLookup type functions, I can't recall ever using single quotes for the parameters. Maybe you have use doubles and escape them, like "" and """"?
    Can't test, gotta run now. My advice is to debug.print your sql and copy/paste into a new query if the output problem isn't obvious, and test there. If this were anything other than a select query and the method being used was DoCmd.Execute, I'd suspect other reasons.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    sgthuth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Location
    SC
    Posts
    33
    You could change the format in the comparison if they are both dates.
    & "WHERE (Int(Format(LoadTested, 'yyyymmdd')) = Int(Format(" & Var_Date & ", 'yyyymmdd')));"

    Also, do you need all those fields in the query?

  14. #14
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Referring back to your original post:

    The error is I think due to the way you are using Var_Date. You have declared it as a Date variable, and given it a value - both of which are fine.

    But look at this part of your SQL:

    sSql = sSql & " WHERE (((tbl_343sTested.TestedDate)=[Var_Date]) AND
    ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]=#' & [Var_Date] & '#')))"

    The whole string is enclosed in only one pair of double-quotes, including [Var_Date]; because [Var_Date] is inside the double-quotes, it is passed to the SQL statement as is, and the substitution for it is not done. So, when Access encounters [Var_Date], it cannot find it as a valid field name anywhere, so you get that error.

    (It IS misleading I know, but error 3061 is what you usually get when you misspell a field name or use a name that is not in the referenced table/query. And you cannot use a variable name in that context, either)

    So, what you have to do is take the references to [Var_Date] outside of the double quotes so the value substitution will work, and use # to delimit the date values, something like this (I hope):

    sSql = sSql & " WHERE (((tbl_343sTested.TestedDate)= #" & [Var_Date]) & "# AND
    ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]=' #" & [Var_Date] & "#)))"

    Give that a shot a see what the debug.print shows as the SQL.

  15. #15
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I have gotten this far with this problem. This is the debug.print from the immediate window. It looks like the first half of the where clause is working but the second half is still not substituting the variable for Var_Date.

    @Micron I dont understand what you did to use the T in your code to shorten the SQL.

    @sgthuth yes they are all dates. all those fields go into a recordset that is used to repopulate a form with unfinished records (9 records at a time). What i am trying to get out of the table is all 9 unfinished records that have the highest date and highest load number from the table. Will that code you offered give me that?

    @John_G I tried redoing the code you have at the end of your post and it gives me a syntax error. I kind of understand what you are saying about substituting the value for Var_Date.


    WHERE (((tbl_343sTested.TestedDate)=#11/8/2017#) AND ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]=#' & [Var_Date] & '#)))

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 3061. Too few parameters. Expected 1.
    By Glenn_Suggs in forum Programming
    Replies: 5
    Last Post: 02-03-2015, 12:03 PM
  2. Error 3061 - Too Few parameters
    By DetrieZ in forum Programming
    Replies: 2
    Last Post: 07-09-2013, 12:52 PM
  3. Replies: 3
    Last Post: 04-26-2013, 01:37 PM
  4. Too Few Parameters, Error 3061
    By uncletreetrunk in forum Programming
    Replies: 7
    Last Post: 08-07-2012, 04:50 PM
  5. Error 3061 Too Few parameters
    By gopherking in forum Programming
    Replies: 5
    Last Post: 10-06-2011, 11:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums