Re: Need to search by date, but do not want the date reflected in results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Drag the OrderDate field into the grid a 2nd time.
In the Total row under this field, change Group BY to Where.
Remove the criteria from the earlier field, and put them here.

You should end up with a SQL statement that has a WHERE clause in the middle
instead of a HAVING clause at the end.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Michael" <michael@xxxxxxxxxxxxx> wrote in message
news:e1IGtEH4FHA.2424@xxxxxxxxxxxxxxxxxxxxxxx
> Thanks Allen
> Using the following SQL, I can limit the dates shown and that is helpful,
> but when I open the range to include all the items or even only two days,
> the items that have entries on 2 different days will still show the total
> for those days and not one total for all consumed.
> Michael
>
> PARAMETERS [Forms].[frmWhatDateConsume].[txtStartDate] DateTime,
> [Forms].[frmWhatDateConsume].[txtEndDate] DateTime;
> SELECT Sum(([colli x Pedana]*[desc])) AS vas, consumenew.confezione,
> Sum(consumenew.[consume film]) AS [SumOfconsume film]
> FROM consumenew
> GROUP BY consumenew.confezione, consumenew.orderdate
> HAVING (((consumenew.orderdate) Between
> [Forms].[frmWhatDateConsume].[txtStartDate] And
> [Forms].[frmWhatDateConsume].[txtEndDate]));


.

Relevant Pages

  • Re: Need to search by date, but do not want the date reflected in results
    ... Michael ... > Allen Browne - Microsoft MVP. ... rather than allenbrowne at mvps dot org. ... >> FROM consumenew ...
    (microsoft.public.access.reports)
  • Re: "Where" column limits records regardless of row
    ... me know if this is correct: Any criteria placed in a column with GROUP BY (or ... any of the aggregrate functions) will fall under the HAVING clause in SQL, ... > Allen Browne - Microsoft MVP. ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.queries)
  • Re: Report Parameters - no results
    ... Allen Browne - Microsoft MVP. ... I did use the WHERE clause you suggested, and now I'm getting the "wrong ... > What else can I do to try to correct my sql? ... >> the WHERE clause in your query. ...
    (microsoft.public.access.reports)
  • Re: Try to code a Command Button "Clear List"
    ... > Dim varItem As Variant ... > Allen Browne - Microsoft MVP. ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.formscoding)
  • Re: Using Allen Brownes calendar in an event procedure
    ... Most important was not altering the return ... But it still has to be a function to work in the event property, ... Allen Browne - Microsoft MVP. ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.modulesdaovba)