Results 1 to 9 of 9
  1. #1
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35

    Query Failure in VBA

    Hi All,



    I have a a bit sloppy, but functional query below that works perfectly fine in Access, but when I try to open the query in vba using [openrecordset] I get a "3061 too few parameters; expected 1" error. Originally, I had the SQL hardcoded in VBA as a string, but to prevent errors, I made it a saved query which is still failing. Any ideas here? Would love to post the DB, but it will be way too time consuming to strip. Will try to provide as much additional information as needed though.

    Thanks


    VBA:
    Code:
    Dim rstQueryEmailNames      As DAO.Recordset
    Set rstQueryEmailNames = CurrentDb.OpenRecordset("qryEmailNamesFilteredTeam")
    SQL:
    Code:
    SELECT DISTINCT [myHelperQry].[USER ID], [myHelperQry].PersonnelID, [myHelperQry].FirstName, [myHelperQry].LastName
    FROM (SELECT DISTINCT tblPersonnel.PersonnelID, Team, tblPersonnel.USER_ID AS [USER ID], tblPersonnel.Last_Name AS LastName, tblPersonnel.First_Name AS FirstName, IIf([tblPersonnelTrainingExemptions]![PersonnelID]=[qryAllDistinctTrainings]![PersonnelID] And [tblPersonnelTrainingExemptions]![TrainingID]=[qryAllDistinctTrainings]![TrainingID],'Exempt',IIf([Date Last Completed] Is Null,'Pending',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]<0,'Expired',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]>=0 And [Days Remaining]<=75,'Expires Soon','Completed')))) AS Status, qryAllPersonnelTrainingReportHelper.[Date Last Completed], IIf([Date Last Completed] Is Null,0,IIf([LifeExpectancyDays] Is Null,999,DateDiff('d',Date(),[Date Last Completed]+[LifeExpectancyDays]))) AS [Days Remaining], tblPersonnel.Active FROM ((tblPersonnel LEFT JOIN qryPersonnelOrganization ON tblPersonnel.PersonnelID = qryPersonnelOrganization.PersonnelID) INNER JOIN ((qryAllDistinctTrainings LEFT JOIN qryAllPersonnelTrainingReportHelper ON (qryAllDistinctTrainings.PersonnelID = qryAllPersonnelTrainingReportHelper.PersonnelID) AND (qryAllDistinctTrainings.TrainingID = qryAllPersonnelTrainingReportHelper.TrainingID)) INNER JOIN tblTrainingList ON qryAllDistinctTrainings.TrainingID = tblTrainingList.TrainingID) ON tblPersonnel.PersonnelID = qryAllDistinctTrainings.PersonnelID) LEFT JOIN tblPersonnelTrainingExemptions ON (qryAllDistinctTrainings.PersonnelID = tblPersonnelTrainingExemptions.PersonnelID) AND (qryAllDistinctTrainings.TrainingID = tblPersonnelTrainingExemptions.TrainingID))  AS [myHelperQry]
    WHERE ((([myHelperQry].Status)<>'Completed') AND (([myHelperQry].Team)=[Forms]![frmMain]![txtTeamFilter]));

  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
    OpenRecordset can't resolve the form reference in the WHERE clause. You can try wrapping the form reference in the Eval() function. If you switch back to having the SQL in VBA, you'd concatenate that value into the string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35
    Thanks for the response, Paul.

    I put the SQL back into a string in VBA and wrapped it in an eval, but got a "7956 the syntax in the subquery is incorrect" error. I triple checked the code and it is the exact same as the SQL that works for me below. Regardless though, even if it did work, how would I be able to reference the information for later calls in my subroutine? Does it read into an array, a temporary table, recordset...?

    I'm not sure of the exact syntax (because I know the below doesn't work), but would it work if I could covert the SQL into a non-nested set, like below?

    Code:
     SELECT DISTINCT tblPersonnel.PersonnelID, Team, tblPersonnel.USER_ID AS [USER ID], tblPersonnel.Last_Name AS LastName, tblPersonnel.First_Name AS FirstName, IIf([tblPersonnelTrainingExemptions]![PersonnelID]=[qryAllDistinctTrainings]![PersonnelID] And [tblPersonnelTrainingExemptions]![TrainingID]=[qryAllDistinctTrainings]![TrainingID],'Exempt',IIf([Date Last Completed] Is Null,'Pending',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]<0,'Expired',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]>=0 And [Days Remaining]<=75,'Expires Soon','Completed')))) AS Status, qryAllPersonnelTrainingReportHelper.[Date Last Completed], IIf([Date Last Completed] Is Null,0,IIf([LifeExpectancyDays] Is Null,999,DateDiff('d',Date(),[Date Last Completed]+[LifeExpectancyDays]))) AS [Days Remaining], tblPersonnel.Active FROM ((tblPersonnel LEFT JOIN qryPersonnelOrganization ON tblPersonnel.PersonnelID = qryPersonnelOrganization.PersonnelID) INNER JOIN ((qryAllDistinctTrainings LEFT JOIN qryAllPersonnelTrainingReportHelper ON (qryAllDistinctTrainings.PersonnelID = qryAllPersonnelTrainingReportHelper.PersonnelID) AND (qryAllDistinctTrainings.TrainingID = qryAllPersonnelTrainingReportHelper.TrainingID)) INNER JOIN tblTrainingList ON qryAllDistinctTrainings.TrainingID = tblTrainingList.TrainingID) ON tblPersonnel.PersonnelID = qryAllDistinctTrainings.PersonnelID) LEFT JOIN tblPersonnelTrainingExemptions ON (qryAllDistinctTrainings.PersonnelID = tblPersonnelTrainingExemptions.PersonnelID) AND (qryAllDistinctTrainings.TrainingID = tblPersonnelTrainingExemptions.TrainingID)
    WHERE (((Status)<>'Completed') AND ((Team)=[Forms]![frmMain]![txtTeamFilter]));

  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
    What does the VBA look like? If the query works stand-alone, it should work in VBA to open the recordset, but you'd need to concatenate the form reference into the string:

    ..."WHERE Field = " & Forms!FormName.TextboxName & " Blah"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35
    I got it to work with the below. Seems like I needed to let the form value be evaluated in VBA rather than the SQL and use the string from the VBA eval in the SQL.

    Edit for future reader clarity: This: ((myHelperQry.Team)= '" & [Forms]![frmMain]![txtTeamFilter] & "') Versus: ((myHelperQry.Team)= [Forms]![frmMain]![txtTeamFilter])

    Code:
    "SELECT DISTINCT myHelperQry.[USER ID], myHelperQry.PersonnelID, myHelperQry.FirstName, myHelperQry.LastName " & _
    "FROM (SELECT DISTINCT tblPersonnel.PersonnelID, Team, tblPersonnel.USER_ID AS [USER ID], tblPersonnel.Last_Name AS LastName, tblPersonnel.First_Name AS FirstName, IIf([tblPersonnelTrainingExemptions]![PersonnelID]=[qryAllDistinctTrainings]![PersonnelID] And [tblPersonnelTrainingExemptions]![TrainingID]=[qryAllDistinctTrainings]![TrainingID],'Exempt',IIf([Date Last Completed] Is Null,'Pending',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]<0,'Expired',IIf(IsNumeric([Days Remaining])=True And [Days Remaining]>=0 And [Days Remaining]<=75,'Expires Soon','Completed')))) AS Status, qryAllPersonnelTrainingReportHelper.[Date Last Completed], IIf([Date Last Completed] Is Null,0,IIf([LifeExpectancyDays] Is Null,999,DateDiff('d',Date(),[Date Last Completed]+[LifeExpectancyDays]))) AS [Days Remaining], tblPersonnel.Active FROM ((tblPersonnel LEFT JOIN qryPersonnelOrganization ON tblPersonnel.PersonnelID = qryPersonnelOrganization.PersonnelID) " & _
    "INNER JOIN ((qryAllDistinctTrainings LEFT JOIN qryAllPersonnelTrainingReportHelper ON (qryAllDistinctTrainings.TrainingID = qryAllPersonnelTrainingReportHelper.TrainingID) AND (qryAllDistinctTrainings.PersonnelID = qryAllPersonnelTrainingReportHelper.PersonnelID)) INNER JOIN tblTrainingList ON qryAllDistinctTrainings.TrainingID = tblTrainingList.TrainingID) ON tblPersonnel.PersonnelID = qryAllDistinctTrainings.PersonnelID) LEFT JOIN tblPersonnelTrainingExemptions ON (qryAllDistinctTrainings.TrainingID = tblPersonnelTrainingExemptions.TrainingID) AND (qryAllDistinctTrainings.PersonnelID = tblPersonnelTrainingExemptions.PersonnelID))  AS myHelperQry " & _
    "WHERE (((myHelperQry.Status)<>'Completed') AND ((myHelperQry.Team)= '" & [Forms]![frmMain]![txtTeamFilter] & "') AND ((myHelperQry.Active)=-1));"

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's what I've been suggesting. Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35
    Ha! I just saw your previous response. Love when that happens. Thanks again for the input.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. Presumably the data type of Team is text, thus the single quotes would have been necessary.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35
    Indeed. Future readers, be sure to consider your data type when using quotes.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. One to many relationship failure
    By gori1084 in forum Queries
    Replies: 8
    Last Post: 05-27-2014, 02:52 PM
  2. Update query failure
    By Santosh in forum Access
    Replies: 1
    Last Post: 08-30-2012, 06:26 AM
  3. Replies: 6
    Last Post: 06-14-2012, 03:39 PM
  4. Replies: 2
    Last Post: 04-25-2012, 08:27 AM
  5. f4 combo box failure
    By djuplina in forum Access
    Replies: 2
    Last Post: 09-19-2011, 01:03 PM

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