Report from a query

[Pusher]

Hi,

I need a report that gets data from a qry that is defined by a combo box and date and time
Do I need to make a form that makes a qry and can I make a button for getting that report in that form – or is there another way of doing this.

 

[CBrighton]

The easiest way is set the basic details in the query (which fields to return, any calculated fields which will be used regardless of criteria selected, etc) then use a series of comboboxes / check boxes / text boxes and a command button to create a string in VBA to apply to the .filter of the report / form which you open.

e.g.

Dim strSQLFilter As String
strSQLFilter = ""
 
'Creates SQL for .filter
 
If Not IsNull(cbbStructureTeam) And Not cbbStructureTeam = "" Then
    strSQLFilter = strSQLFilter & "TeamID = '" & cbbStructureTeam & "' AND "
End If
If Not IsNull(cbbStructureSection) And Not cbbStructureSection = "" Then
    strSQLFilter = strSQLFilter & "SectionID = '" & cbbStructureSection & "' AND "
End If
If Not IsNull(cbbStructureDepartment) And Not cbbStructureDepartment = "" Then
    strSQLFilter = strSQLFilter & "DepartmentID = '" & cbbStructureDepartment & "' AND "
End If
If Not IsNull(cbbStructureArea) And Not cbbStructureArea = "" Then
    strSQLFilter = strSQLFilter & "AreaID = '" & cbbStructureArea & "' AND "
End If
 
'Use combobox to allow between, more than or less than criteria on numeric fields
If Not IsNull(txtHours1) And Not txtHours1 = "" Then
    If cbbHoursControl = "Between" Then
        If IsNull(txtHours2) And Not txtHours2 = "" Then
            MsgBox "Check hour selections."
        End If
        strSQLFilter = strSQLFilter & "Hours Between #" & txtHours1 & _
                           "# AND #" & txtHours2 & "# AND "
    ElseIf cbbHoursControl = "Less than" Then
        strSQLFilter = strSQLFilter & "Hours < #" & txtHours1 & "# AND "
    ElseIf cbbHoursControl = "More than" Then
        strSQLFilter = strSQLFilter & "Hours > #" & txtHours1 & "# AND "
    End If
End If
 
'Trim off trailing " AND "
If Len(strSQLFilter) > 6 Then
    strSQLFilter = Left(strSQLFilter, Len(strSQLFilter) - 5)
End If
 
'Open specified report based on cascading comboboxes
'Report name pulled from the bound (but zero-width) columns in the combo boxes
'[B]strSQLFilter[/B] is applied as the filter in OpenReport
If cbbReportType = "General" Or Right(cbbReportType2, 4) = "list" Then
    DoCmd.OpenReport cbbReportType2.Column(1), acPreview, , [B]strSQLFilter[/B]
Else
    DoCmd.OpenReport cbbReportType2.Column(1) & cbbReport.Column(1), acPreview, , [B]strSQLFilter[/B]
End If