Passing value to MS Access parameter

Question

I have a little issue with passing value to Query Parameter.

I want to pass a value using VBA. This is how my query goes:

PARAMETERS [WC Date] DateTime;
SELECT g1.Site_ID, g1.Site_Name, g1.Visits, g1.[Planned Turn], g1.[Avg Planned Turn], g1.[Actual Turn], g1.[Avg Act Turn], g1.[Arrival Variance], g1.[Departure Variance], g1.Week1, g1.Week2, g1.Week3, g1.Week4
FROM (SELECT geo.Site_ID, geo.Site_Name, Count(geo.Site_ID) AS Visits, Sum(DateDiff("n",[Planned_Arrival],[Planned_Departure])) AS [Planned Turn], Round([Planned Turn]/[Visits],2) AS [Avg Planned Turn], Sum(DateDiff("n",[Actual_Arrival],[Actual_Departure])) AS [Actual Turn], Round([Actual Turn]/[Visits],2) AS [Avg Act Turn], Round(Avg(DateDiff("n",[Planned_Arrival],[Actual_Arrival])),2) AS [Arrival Variance], Round(Avg(DateDiff("n",[Planned_Departure],[Actual_Departure])),2) AS [Departure Variance], wk1.Week1, wk2.Week2, wk3.Week3, wk4.Week4
FROM (((T_Geo AS geo LEFT JOIN (SELECT 
                        w1.Site_ID, w1.Site_Name, 
                        Count(w1.Site_ID) AS Visits, 
                        Sum(DateDiff("n",[Actual_Arrival],[Actual_Departure])) AS [Actual Turn], 
                        Round([Actual Turn]/[Visits],2) AS Week1
                    FROM T_Geo w1
                    WHERE (((Int([Planned_Arrival]))>=[WC Date]-28 And (Int([Planned_Arrival]))<[WC Date]-21))
                    GROUP BY w1.Site_ID, w1.Site_Name
                    )  AS wk1 ON geo.Site_ID = wk1.Site_ID) LEFT JOIN (SELECT 
                    w2.Site_ID, w2.Site_Name, 
                    Count(w2.Site_ID) AS Visits, 
                    Sum(DateDiff("n",[Actual_Arrival],[Actual_Departure])) AS [Actual Turn], 
                    Round([Actual Turn]/[Visits],2) AS Week2
                FROM T_Geo w2
                WHERE (((Int([Planned_Arrival]))>=[WC Date]-21 And (Int([Planned_Arrival]))<[WC Date]-14))
                GROUP BY w2.Site_ID, w2.Site_Name
                )  AS wk2 ON geo.Site_ID = wk2.Site_ID) LEFT JOIN (SELECT 
                w3.Site_ID, w3.Site_Name, 
                Count(w3.Site_ID) AS Visits, 
                Sum(DateDiff("n",[Actual_Arrival],[Actual_Departure])) AS [Actual Turn], 
                Round([Actual Turn]/[Visits],2) AS Week3
            FROM T_Geo w3
            WHERE (((Int([Planned_Arrival]))>=[WC Date]-14 And (Int([Planned_Arrival]))<[WC Date]-7))
            GROUP BY w3.Site_ID, w3.Site_Name
            )  AS wk3 ON geo.Site_ID = wk3.Site_ID) LEFT JOIN (SELECT 
        w4.Site_ID, w4.Site_Name, 
        Count(w4.Site_ID) AS Visits, 
        Sum(DateDiff("n",[Actual_Arrival],[Actual_Departure])) AS [Actual Turn], 
        Round([Actual Turn]/[Visits],2) AS Week4
    FROM T_Geo w4
    WHERE (((Int([Planned_Arrival]))>=[WC Date]-7 And (Int([Planned_Arrival]))<[WC Date]))
    GROUP BY w4.Site_ID, w4.Site_Name
    )  AS wk4 ON geo.Site_ID = wk4.Site_ID
WHERE (Int([Planned_Arrival])>=[WC Date] And Int([Planned_Arrival])<=[WC Date]+6) AND IsNumeric(geo.Site_ID) = True
GROUP BY geo.Site_ID, geo.Site_Name, wk1.Week1, wk2.Week2, wk3.Week3, wk4.Week4
)  AS g1
GROUP BY g1.Site_ID, g1.Site_Name, g1.Visits, g1.[Planned Turn], g1.[Avg Planned Turn], g1.[Actual Turn], g1.[Avg Act Turn], g1.[Arrival Variance], g1.[Departure Variance], g1.Week1, g1.Week2, g1.Week3, g1.Week4
ORDER BY g1.[Avg Act Turn] DESC;

I tried to use the following code:

Public Function SendReport()

    Dim reportName As String, path As String
    Dim wcDate As Date

    reportName = "Geofence_Analysis"
    wcDate = Date - Weekday(Date, vbSunday) + 1 - 7
    path = "C:\users\" & Environ("USERNAME") & "\desktop\" & reportName & "_" & Format(wcDate, "dd-mm-yyyy") & ".pdf"
    DoCmd.OpenReport reportName, acViewPreview, , , acWindowNormal, [WC Date] = wcDate
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, path

End Function

But it's throwing an error as [WC Date] is not recognizable field in my query.

I know I can get the same result setting up the form and having a parameter there and just reference the form field to my query but I would like to achieve that from the code...

Any ideas?

Thanks Piotr

Passing value to MS Access parameter

Answers (1)

Related Questions