Dynamic Access Query - Select Specific Fields

Another way I've found useful is to create the SQL dynamically.  I have a report called Custom that when it's opened opens a form with drop-downs for specific values and textboxes for search text (Like statement).  I also let them enter a custom report header if they want.  

Once they pick the criteria they want they click on OK.  The form has the main SQL in a variable without the Where stuff.  The code then checks each thing they could have set and builds the SQL and puts it in the tag property of the form and hides the form.  The important thing to make this work is to open the form as a dialog so that it is modal and the calling code (report) doesn't continue execution until control is returned.  

Once control is back on the report it looks at the hidden form's tag property and pulls out the info if anything is there.  Using that it finishes the SQL and sets the reports data source with it and also the report header.  And of course it closes the hidden form.  Looks like a lot, but its very clean for your users.

'Report Code
Private Sub Report_Open(Cancel As Integer)
   
    DoCmd.OpenForm "dlgCustomRpt", acNormal, , , acFormEdit, acDialog
    If Forms![dlgCustomRpt].Tag <> "" Then
        Me.RecordSource = Forms![dlgCustomRpt].Tag
        If Not IsNull(Forms![dlgCustomRpt].txtRptTitle) Then
            Me.lblTitle.Caption = Forms![dlgCustomRpt].txtRptTitle
            Me.lblTitle2.Caption = Forms![dlgCustomRpt].txtRptTitle
        End If
        WhereSQL = "Release = " & Forms![dlgCustomRpt].cboRelease.Column(1) & ", "
        WhereSQL = WhereSQL & "Type = " & Forms![dlgCustomRpt].cboType.Column(1) & ", "
        WhereSQL = WhereSQL & "Status = " & Forms![dlgCustomRpt].cboStatus.Column(1) & ", "
        WhereSQL = WhereSQL & "BA = " & Forms![dlgCustomRpt].cboBA.Column(1)
        Me.lblSQL.Caption = WhereSQL
        DoCmd.ShowToolbar "Print Preview", acToolbarYes
        DoCmd.Close acForm, "dlgCustomRpt"
    Else
        DoCmd.Close acForm, "dlgCustomRpt"
        Cancel = True
    End If
   
End Sub


'Form Code
Option Compare Database

Private Const strSQLFields = "SELECT tblChangeControls.[ID], tblChangeControls.[Record Status], " _
                             & "[tblRecord Types].RecTypeDesc, tblChangeControls.[Contact Name] AS Requestor, " _
                             & "tblChangeControls.[BA Name], tblChangeControls.[Record Name] AS [CC Name], " _
                             & "tblChangeControls.Description, tblChangeControls.[Target Release], " _
                             & "tblChangeControls.[Progress/Notes], tblChangeControls.[Open Date], tblChangeControls.Update_Date, " _
                             & "tblChangeControls.[Close Date], tblStatusCodes.StatusSort, " _
                             & "tblProjGroup.prjgNumber, tblProjGroup.prjgName " _
                             & "FROM ((tblChangeControls LEFT JOIN [tblRecord Types] ON tblChangeControls.Type = [tblRecord Types].RecTypeCode) " _
                             & "LEFT JOIN tblStatusCodes ON tblChangeControls.[Record Status] = tblStatusCodes.StatusCode) LEFT JOIN tblProjGroup " _
                             & "ON tblChangeControls.prjgID = tblProjGroup.prjgID " _
                             & "WHERE (IIf(DateDiff('d',Date(),[Close Date])>30 Or IsNull([Close Date]),'T','F'))='T' "
                                                       
Private Sub cmdCancel_Click()
    Me.Tag = ""
    Me.Visible = False
End Sub

Private Sub cmdOk_Click()
Dim ID_No As Variant, Pos As Integer
   
    Me.Tag = strSQLFields
   
    If Len(Me.txtCC) > 0 Then
        'Clean up trailing comma
        If Right$(Me.txtCC, 1) = "," Then
            ID_No = Mid$(ID_No, 1, Len(Me.txtCC) - 1)
        End If
        Pos = InStr(Me.txtCC, ",")
        If Pos > 0 Then
            Me.Tag = Me.Tag & "AND [tblChangeControls].[ID] In (" & Me.txtCC & ") "
        Else
            Me.Tag = Me.Tag & "AND [tblChangeControls].[ID]= " & Me.txtCC & " "
        End If
    End If
    If Len(Me.txtDescription) > 0 Then
        Me.Tag = Me.Tag & "AND [tblChangeControls].[Description] Like '*" & Me.txtDescription & "*' "
    End If
    If Len(Me.txtProgress) > 0 Then
        Me.Tag = Me.Tag & "AND [tblChangeControls].[Progress/Notes] Like '*" & Me.txtProgress & "*' "
    End If
    If Me.cboRelease <> 0 Then
        Me.Tag = Me.Tag & "AND [tblChangeControls].[Target Release]= " & Me.cboRelease.Column(0) & " "
    End If
    If Me.cboStatus <> 0 Then
        Me.Tag = Me.Tag & "AND [tblStatusCodes].[StatusCode]= " & Me.cboStatus.Column(0) & " "
    End If
    If Me.cboType <> 0 Then
        Me.Tag = Me.Tag & "AND [tblRecord Types].[RecTypeCode]= " & Me.cboType.Column(0) & " "
    End If
    If Me.cboBA <> 0 Then
        Me.Tag = Me.Tag & "AND [tblChangeControls].[BA Name]= '" & Me.cboBA.Column(0) & "' "
    End If
    If Len(Me.cboRequestor) > 0 Then
        Me.Tag = Me.Tag & "AND [tblChangeControls].[Contact Name]= '" & Me.cboRequestor.Column(0) & "' "
    End If
    If Len(Me.cboProjectGroup) > 0 Then
        Me.Tag = Me.Tag & "AND [tblProjGroup].[prjgID]= " & Me.cboProjectGroup.Column(0)
    End If
    Me.Visible = False
       
End Sub

Private Sub cmdReset_Click()
    Me.cboBA = 0
    Me.cboRelease = 0
    Me.cboStatus = 0
    Me.cboType = 0
    Me.txtDescription = ""
    Me.txtCC = ""
    Me.txtProgress = ""
    Me.txtRptTitle = "Custom Report"
End Sub

Private Sub Form_Load()
    Me.cboBA = 0
    Me.cboRelease = 0
    Me.cboStatus = 0
    Me.cboType = 0
    Me.txtDescription = ""
    Me.txtCC = ""
    Me.txtProgress = ""
    Me.txtRptTitle = "Custom Report"
End Sub

To start with, your db design is flawed.  You should never have fields like Jul06, Aug06, Sep06, etc.

Instead, consider:

ProdID
PartID
PDate
Price

DB Engines go down a table at considerable speed, but really slow down when it has to 'compute' across records.  Comments?