Modifying Query from Query Wizard

[dccjr3927]

I have created the following query with the wizard, but need to modify it for use in VBA.

I have:

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:

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:

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?

[ranman256]

don't use VBA, modify the query in query design.
that way there's no error.
(but date format for a date field is mm/dd/yyyy, even tho you don't want to see it that way)

[dccjr3927]

I am running the report from a button after selections are made on the form (start date, end date, and contract company). I am not sure how to pass the values from the form to the query.

[isladogs]

For a start, add a space before 'FROM WOTracking'.

[Micron]

Consider basing the report on a simple Select query without a WHERE clause and then applying a filter to the report when you code to open the report. Use the grouping, summing and sorting features of the report instead of trying to do it in a Totals query. Or start over again with the wizard and apply the Where criteria. Or use query design rather than just trying to append a Where clause. That approach fails because this is a Totals query - you just can't append a normal Where clause to what you have.

[isladogs]

I would agree about using the query designer to get this working. You can easily convert back to SQL afterwards.

Just to clarify, you can use either HAVING or WHERE as filters in an aggregate (totals) query.
In fact its possible to do both at the same time...
The difference is that HAVNG is applied before the grouping is done, WHERE afterwards.

Both work but using HAVING is faster if the search fields are indexed. The speeds are similar if indexes aren't used.
For examples, see http://www.mendipdatasystems.co.uk/s...s-4/4594459445 and http://www.mendipdatasystems.co.uk/s...s-8/4594556613

[dccjr3927]

Thanks guys. I got side tracked, but will try the suggestions this week. I really appreciate all of the assistance.