Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Click image for larger version. 

Name:	Capture.JPG 
Views:	9 
Size:	19.7 KB 
ID:	31642
    i took the debug print from the immediate window and pasted it into a new query design and got this error.

    I would like to thank all of you for your help. I have learned a lot about using query design and immediate windows to troubleshoot problems.



    Walker

  2. #17
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Sorry - I didn't have the syntax quite right.

    Try this version:


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


    After setting Var_Date = Date, I tested it in the immediate pane and got this for the WHERE:

    WHERE ((tbl_343sTested.TestedDate)= #2017-12-13# AND
    ((tbl_343sTested.LoadTested)=DMax('LoadTested','tb l_343sTested','[tbl_343sTested.TestedDate]= #2017-12-13#')))

    This puts the criteria part of the DMAX function entirely inside one set of single quotes, which is fine because the date is hard-coded.

    Don't worry about the date format of #2017-12-13# - I have set my system settings to use that format, and it works because it has the 4-digit year. If you use mm/dd/yy, it should be fine.

    Note: Don't enclose Var_Date in square brackets ([]) - you will probably get an error (I did)

  3. #18
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    John,

    Thank you that worked. I also took a different approach to fix it because i felt like i was getting nowhere with the other way so i removed the dmax part from the sql where statement and made it its own above the sql statement like the Var_Date dmax. then I made the where statement equal to each of those variables. It seemed so easy that way. I dont know why I didnt think of that sooner.

    Thank you all for your help.

    Here is my code and how I fixed it for anyone to look at later.

    Code:
    Private Sub btnInProc_Click()
        'On Error GoTo EH
        Dim sSql As String
        Dim Var_Date As Date
        Dim Var_Load As String
        
        Var_Date = DMax("TestedDate", "tbl_343sTested")
        Var_Load = DMax("LoadTested", "tbl_343sTested", "(tbl_343sTested.TestedDate)=#" & Var_Date & "#")
        txtVar_Date = Var_Date
        Debug.Print "<" & Var_Date & ">"
        Debug.Print "<" & Var_Load & ">"
        DoCmd.Hourglass True
            sSql = "SELECT tbl_343sTested.FixturePosition, tbl_343sTested.SerialNumber, tbl_343sTested.TestedDate, tbl_343sTested.Technician, " _
                & " tbl_343sTested.TankTestedIn, tbl_343sTested.LoadTested, tbl_343sTested.PrePressureReading, tbl_343sTested.PrePressureUnits, tbl_343sTested.PreReading, " _
                & " tbl_343sTested.HighPressureReading, tbl_343sTested.HighPressureUnits, tbl_343sTested.HighReading, tbl_343sTested.PostPressureReading, " _
                & " tbl_343sTested.PostPressureUnits, tbl_343sTested.PostReading, tbl_343sTested.Recheck, tbl_343sTested.PassFail, tbl_343sTested.Tr343Id, " _
                & " tbl_343sTested.MeggerID, tbl_343sTested.PressureIndID, tbl_343sTested.Comment " _
                & " FROM tbl_343sTested " _
                & " WHERE (((tbl_343sTested.TestedDate)=#" & Var_Date & "#) AND ((tbl_343sTested.LoadTested)=" & Var_Load & "))"
                
        
        Debug.Print sSql
        Set dbTest = CurrentDb
        Set rstTestData = dbTest.OpenRecordset(sSql)
    Last edited by NightWalker; 12-13-2017 at 04:12 PM. Reason: to thank you all

  4. #19
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by NightWalker View Post
    @Micron I dont understand what you did to use the T in your code to shorten the SQL.
    I gave your table an alias (on the fly). To see what I mean, create a new query with 1 field from 1 table. Switch to sql view an see what's there. With the property sheet visible, click on the table header and look at the property sheet Alias row of the General tab. Change the alias value to something else and see what happens to the table header and read the sql again.
    Sometimes you must alias a table - like when you have to include the same table twice in a query, or a query contains a sub query. Or just when you don't feel like repeating 26 letters for one table name over and over. None of this has any effect on the table's original name.

  5. #20
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thank you Micron. I will try to use that in future projects.

Page 2 of 2 FirstFirst 12
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