How can I group on a field in a query based on a value in a form?

[sschulte41]

I have a form which has a macro to open a query. I also have check boxes on
the form so the user can check which fields they query results will be
grouped on. So if the box is not checked, the query should not be grouped on
that particular field. How can I write a macro to do this?

 

[Steve Schapel]

I am sorry, Sschulte, this is not possible with a macro. It may,
however, be possible to use a VBA provedure to construct a SQL string
according to the checkbox selections. If you need more specific help
with this, please post back with more detail of the data and the
required query, with examples.

 

[SSchulte]

Hi Steve,
Thanks for your response. Here are the specifics: The query will sum the
advertising expense field in my table. There are check boxes for all the
different fields that the query could group on. So on the form, there is a
check box for "run date", "month", "Market", and "newspaper". These are all
fields in the table that the query is based on. So if no check boxes are
checked, the query should return 1 number - the total sum of expense. If just
"month" is checked, the query should return total expense by month, and so
on. Is this enough detail?

Thanks,
Sara

 

[Steve Schapel]

Sara,

Here is some "air code" which hopefully will get you pointed in the
right direction...

Dim FieldsToInclude As String
Dim strSQL As String
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("NameOfYourQuery")
If Me.RunDateGrouper Then ' your checkbox
FieldsToInclude = "[run date],"
End If
If Me.MonthGrouper Then
FieldsToInclude = FieldsToInclude & "[month],"
End If
... etc
strSQL = "SELECT " & FieldsToInclude & "Sum([Advertising Expense])" & _
" FROM [my table]"
If Len(FieldsToInclude) Then
strSQL = strSQL & " GROUP BY " & FieldsToInclude
' remove trailing comma
strSQL = Left(strSQL,Len(strSQL)-1)
End If
qdf.SQL = strSQL
DoCmd.OpenQuery "NameOfYourQuery"