I need help passing parameters to a query in Access from VBA

Hi Experts,
I have the created a query in Access 2013 and named it qryTest3.  The values for some of the fields are hard coded.  I want to be a ble to pass in the parameters for the following fields in my query:  SK.Skill, SK.NumberLevel, and TmpDate.  I want to pass in the values/parameters from my VBA code and place the results in a recordset.  How do I do that?  Do I have to rewrite my query?  Below is a the sample of my query:

SELECT AV.Tech_Name, AV.TmpDate, AV.AVAILABILITY, AV.NOTES, AV.Skill_ID, SK.Skill, SK.Level, SK.NumberLevel
FROM qryAvailability2NEW AS AV INNER JOIN tblSkills AS SK ON SK.SkillID = AV.Skill_ID
WHERE SK.Skill in ('Programmer') AND SK.NumberLevel <= 2
AND (AV.TmpDate >= '2/3/2015' AND AV.TmpDate <= '2/5/2015');

Open in new window



Thank you very much in advance.
mrotor
mainrotorAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
You need to format the date parameters, and it could be simplified a little:

' Your parameter variables.
Dim strProgrammer As String
Dim intNumberLevel As Integer
Dim datDateFrom As Date
Dim datDateTo As Date

' Other variables.
Dim strDateFrom As String
Dim strDateTo As String
Dim strSQL As Date

strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
strDateTo = Format(datDateTo, "yyyy\/mm\/dd")

strSQL = "SELECT AV.Tech_Name, AV.TmpDate, AV.AVAILABILITY, AV.NOTES, AV.Skill_ID, SK.Skill, SK.Level, SK.NumberLevel
FROM qryAvailability2NEW AS AV INNER JOIN tblSkills AS SK ON SK.SkillID = AV.Skill_ID
WHERE SK.Skill = '" & strProgrammer & "' AND SK.NumberLevel <= " & intNumberLevel & " AND (AV.TmpDate Between #" & strDateFrom & "# AND #" & strDateTo & "#)";

To open a recordset:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

/gustav
0
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

strSQL = "SELECT AV.Tech_Name, AV.TmpDate, AV.AVAILABILITY, AV.NOTES, AV.Skill_ID, SK.Skill, SK.Level, SK.NumberLevel
FROM qryAvailability2NEW AS AV INNER JOIN tblSkills AS SK ON SK.SkillID = AV.Skill_ID
WHERE SK.Skill in ('" & Param1 & "') AND SK.NumberLevel <= " & Param2 & "
AND (AV.TmpDate >= #" & Param3 & "# AND AV.TmpDate <= #" & Param4 & "# );

Open in new window


refer to
https://msdn.microsoft.com/en-us/library/dd627355(v=office.12).aspx

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.