Problem with grouping and condition 2

[LARiot]

Hi,

I'm running a summary report where the filter condition is created and the report itself is called from a form. The report uses a query where the fields are grouped.

The problem is that we need to group by the Series but sometimes they have a different Start Date. The Start Date has to be part of the condition used to run the report.

That is:
Series Shows Start Date
Morning Show 1 1/1/2001
Morning Show 1 2/2/2002
Morning Show 1 3/3/2003
Morning Show 1 4/4/2004

If user selects dates from 2/2/2002 until 3/3/2003 then:
Series Shows Start Date
Morning Show 2 (not shown)

I'm finding this imposible with a select query using a totals (Group by).

Any help is greatly appreciated.

Thanks

 

[lameid]

How about the following query?

Select Series, Count(Series) As Shows
From Table
Where [Start Date] Between #2/2/2002# And #3/3/2003#
Group By Series

 

[LARiot]

lameid, thanks but I really should be more clear.

The report is run from VBA using DoCmd.OpenReport. It's WhereCondition is derived from fields on the form which collects the date range for StartDate. The report itselft uses an aggregate query with a few fields including StartDate.

If the StartDate is removed from the aggregate query the report asks the user to enter it (which would run the report no data). If it's not removed it seperates records with different dates and that's also an undesired effect.

I hope that makes sense.

 

[dhookom]

There are times when you might have to put the reference to the controls in the actual SQL of the report's record source query.

SELECT ....
FROM ....
WHERE [StartDate] Between Forms!frmDates!txtStart And Forms!frmDates!txtEnd
GROUP BY ...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

If Duane's answer isn't what you are looking for could you post your SQL statemnt, and better describe what the report is doing versus what you want it to do?

 

[LARiot]

Duane, that's definitely the way to do it. But there's another factor. The form also has two multiselect listboxes. My VBA constructs the WhereCondition like this:

With Me.cboProductSource
  sCondition = "[ProductSource] IN ("
  For i = 0 To .ListCount
    If .Selected(i) Then
      If i >= 1 And sProductSource <> Empty Then
        sCondition = sCondition & ","
      End If
      sProductSource = .ItemData(i)
      sCondition = sCondition & "'" & sProductSource & "'"
    End If
  Next i
  sCondition = sCondition & ")"
End With
    
With Me.cboType
  sCondition = sCondition & " AND [Type] IN ("
  For i = 0 To .ListCount
    If .Selected(i) Then
      If i >= 1 And sType <> Empty Then
        sCondition = sCondition & ","
      End If
      sType = .ItemData(i)
      sCondition = sCondition & "'" & sType & "'"
    End If
  Next i
  sCondition = sCondition & ")"
End With

If Me.cboFrom <> "All" Then
  sCondition = sCondition & " AND [License Term Start Date] >= #" & Me.cboFrom & "#"
End If
    
If Me.cboTo <> "All" Then
  sCondition = sCondition & " AND [License Term Start Date] <= #" & Me.cboTo & "#"
End If

DoCmd.OpenReport cboReport, acViewPreview, , sCondition

Is there a way to add ProductSource and Type to the WhereCondition when it's being constructed in the VBA?

 

[LARiot]

...and BTW, thanks for all the help.

 

[dhookom]

You can add references to form controls filtering against fields that don't appear in your report's field list. And, you can combine this with code like you have listed using the Where Condition of DoCmd.OpenReport.

Another method that I find handy is to build the entire sql statement in code. Either update the SQL property of a saved query (report's record source) or set the Record Source property of the report to the SQL statement during its On Open event.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LARiot]

Duane,

It worked. I built the entire SQL statement in code then updated the SQL property of the report's query.

Here's the code if anyone else needs it:

(This is in addition to the code from one of my previous posts)

sSQL = "SELECT ProductSource, " & _
        "[Series Title], " & _
        "Type, " & _
        "CommercialRunTime, " & _
        "[License Fee], " & _
        "Sum(Quantity) as SumOfQuantity, " & _
        "Sum([License Fee]*[Quantity]) As Total " & _
        "FROM tblCanada " & _
        "WHERE " & sCondition & " " & _
        "GROUP BY ProductSource, " & _
        "[Series Title], " & _
        "Type, " & _
        "CommercialRunTime, " & _
        "[License Fee]"
        CurrentDb.QueryDefs("qrptTopSummary").SQL = sSQL
        DoCmd.OpenReport cboReport, acViewPreview

Thanks again.

 

[lameid]

I get it now... You wanted to know how to pass criteria to the Where clause of your query and not use criteria based on the aggregate result (or equivalently the having clause)...

I'm giving dhookom a star. Just because he could offer a solution without the piece I would have needed.

 

[dhookom]

LARiot,
I like your coding style. Very readable and maintainable.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LARiot]

Thanks.

- LARiot

Thus spake the master programmer:

"After three days without programming,
life becomes meaningless."

-The Tao of Programming
(Book Two, The Ancient Masters)