I have created the following query with the wizard, but need to modify it for use in VBA.
I have:
Code:
SELECT DISTINCTROW Format$([WOTracking].[Date_Time],'Long Date') AS [Date_Time By Day], WOTracking.ContractCo, Sum(WOTracking.SKUQty) AS [Sum Of SKUQty]
FROM WOTracking
GROUP BY Format$([WOTracking].[Date_Time],'Long Date'), WOTracking.ContractCo;
However I would like to limit this with the following:
Code:
WHERE Company = '" & Me.cbxReportCriteria & "' AND ShipDate >= #" & Format(dt1, "yyyy-mm-dd") & "# AND ShipDate <= #" & Format(dt2, "yyyy-mm-dd") & "#;
I tried simply inserting it as follows:
Code:
SELECT DISTINCTROW Format$([WOTracking].[Date_Time],'Long Date') AS [Date_Time By Day], WOTracking.ContractCo, Sum(WOTracking.SKUQty) AS [Sum Of SKUQty]From WOTracking WHERE ContractCo = '" & Me.cbxReportCriteria & "' AND Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time <= #" & Format(dt2, "yyyy-mm-dd") & "# GROUP BY Format$([WOTracking].[Date_Time],'Long Date'), WOTracking.ContractCo;"
I keep getting RunTime 3122 'query does not contain '[WOTracking].[Date_Time] as part of the aggregate function'.
Can someone point out what I am missing?