Re: How can I group on a field in a query based on a value in a fo

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 21:45:41 +1300

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" 

-- 
Steve Schapel, Microsoft Access MVP
SSchulte wrote:
> 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
>

Relevant Pages

  • Re: Is there a way to do "save as" a new record in a form?
    ... This button would run an Append query to append ... Private Sub cmdSaveAsNew_Click ... Dim db As DAO.Database ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: Is there a way to do "save as" a new record in a form?
    ... the purpose of easier data entry. ... This button would run an Append query to append ... Dim db As DAO.Database ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: VBSCRIPT Import CSV File Change Data Type
    ... When you SELECT * from a text file without a header row Jet ... > B)The make table query method does import the data but our IP Address ... > dim oJet ... > dim strSQL ...
    (microsoft.public.access.externaldata)
  • Re: Main Form checkbox toggles many subform checkboxes
    ... Dim db As DAO.Database ... Dim bValue As Boolean ... Dim strSql As String ... To help you get your SQL statement, mock up a query using this table. ...
    (microsoft.public.access.forms)
  • Re: OpenRecordset
    ... query is taking values or criteria from fields on a form (which are treated ... Dim qdf As DAO.QueryDef ... Dim prm As DAO.Parameter ...
    (microsoft.public.access.formscoding)