RWilliams500
New member
- Local time
- Yesterday, 19:16
- Joined
- May 22, 2024
- Messages
- 20
Howdy. I'm messing around with parameters in a query and setting them via VBA and I've hit a problem. I think I'm setting them right, but when I open or execute the query, it still asks for the parameters. If I enter them in the prompt, it works fine. Also, the For Each debug.print section does print both the proper parameter name and value assigned through VBA.
First is the code for the sub that's assigning the parameters and trying to open the query.
Following that is the query itself.
Thanks for any advice.
Query
First is the code for the sub that's assigning the parameters and trying to open the query.
Following that is the query itself.
Thanks for any advice.
Code:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQueryName As String
Dim rs As DAO.RecordSet
Dim prm As DAO.Parameter
Dim sPar1 As String, sPar2 As String
Dim sFreq As String
Dim dDate As Date
sPar1 = "[parExamFreq]"
sPar2 = "[parExamPeriod]"
sFreq = "Weekly"
dDate = Date
strQueryName = "qry_Par_Start_Date_In_Range" ' Replace with your query's name
Set db = CurrentDb
Set qdf = db.QueryDefs(strQueryName)
qdf.Parameters(sPar1).Value = sFreq
qdf.Parameters(sPar2).Value = dDate
Set rs = qdf.OpenRecordset(dbOpenDynaset)
For Each prm In qdf.Parameters
'Now you can access properties of each parameter, like its Name and Value
Debug.Print "Parameter Name: " & prm.Name
Debug.Print "Parameter Value: " & prm.Value
Next prm
'db.Execute strQueryName
DoCmd.OpenQuery strQueryName, acViewNormal
'DoCmd.OpenQuery qdf.Name, acViewNormal
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
Debug.Print "Equipment ID: " & rs!EquipmentID
rs.MoveNext
Loop
End If
' Close the recordset and clean up objects
rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = NothingQuery
SQL:
SELECT
tblEquipment.EquipmentID,
(
DatePart("yyyy", [tblEquipment].[StartOfServiceDate]) * 1000
) + DatePart("ww", [tblEquipment].[StartOfServiceDate]) AS StartDate
FROM
tblEquipment
WHERE
((([parExamFreq]) = "Weekly"))
GROUP BY
tblEquipment.EquipmentID,
(
DatePart("yyyy", [tblEquipment].[StartOfServiceDate]) * 1000
) + DatePart("ww", [tblEquipment].[StartOfServiceDate])
HAVING
(
(
(
(
DatePart("yyyy", [tblEquipment].[StartOfServiceDate]) * 1000
) + DatePart("ww", [tblEquipment].[StartOfServiceDate])
) <= fExamPeriod ([parExamPeriod], "Weekly")
)
);
