I'm stumped over something probably simple. I'm grouping results by month and I want to filter it by a date range. I have a GROUP BY query shown below.
PARAMETERS ShipDateStart DateTime, ShipDateEnd DateTime;
SELECT Month([ORDERS].[ShipDate]) & '/01/' & Right(Year([orders].[ShipDate]),2) AS ShipDateName, Sum(IIf([ORDERS].[ShipDate]-IIf(IsNull([ORDERS].[PromisedDate]),[ORDERS].[EstimatedDate],[ORDERS].[PromisedDate])<=0,1,0)) AS OnTimeYes, Sum(IIf([ORDERS].[ShipDate]-IIf(IsNull([ORDERS].[PromisedDate]),[ORDERS].[EstimatedDate],[ORDERS].[PromisedDate])>0,1,0)) AS OnTimeNo, Year([orders].[ShipDate]) AS ShipDateYear
FROM OrderLineStatus INNER JOIN (DEST INNER JOIN ORDERS ON DEST.DestID = ORDERS.DestID) ON OrderLineStatus.OrderLineStatusID = ORDERS.OrderLineStatusID
WHERE (((DEST.Destination)<>"Purchased" And (DEST.Destination)<>"STOCKED") AND ((OrderLineStatus.OrderLineStatus)="Shipped") AND ((ORDERS.PromisedDate) Is Not Null) AND ((ORDERS.ShipDate) Between [ShipDateStart] And [ShipDateEnd]))
GROUP BY Month([ORDERS].[ShipDate]) & '/01/' & Right(Year([orders].[ShipDate]),2), Year([orders].[ShipDate]), Month([ORDERS].[ShipDate])
ORDER BY Year([orders].[ShipDate]), Month([ORDERS].[ShipDate]);
This query works fine. I enter the two parameters and it returns what I want. The problem is that when I'm opening a report with this query as the record source, it asks for the parameters and I can not figure out a way to pass parameters to the query without Access asking for them. The following does not work and it still asks for the parameters.
sWhere = "[ShipDateStart] = #" & Format(CDate(Me.StartDateBox), "mm/dd/yyyy") & _
" 00:00# AND [ShipDateEnd] = #" & Format(CDate(Me.EndDateBox), "mm/dd/yyyy") & _
" 23:59#"
stDocName = "ShippingOnTimePSD"
DoCmd.OpenReport stDocName, acPreview, , sWhere, acWindowNormal
There are two controls on a form for the dates and a button that runs this code. How can this be done?
