Filter Report based on Form

[steve711]

Hello All,

I am trying to open a report filtered by a date range (that part works fine) and also based on a value from a listbox when the user selects another criteria for the filtered report. That is where the problem is occurring.

I have a report named rpt_DutyTime (under properties filter is on) and the control source is qry_DutyTime.

When I open my form and select my dates and the particular base and click the button to view it I get another form asking for the tblBases. So I put it in manually and it opens the report but with ALL bases not the one that I typed in or selected.

Here is my code if someone could take a look and tell me where I'm going wrong:

Private Sub Command8_Click()
    mybase = Me.lstBases

If Me.ckAll = True Then

    'Routine opens report with ALL the bases in the listbox and the selected date range
    
        On Error GoTo Err_runQuery_Click
    
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.OpenReport "rpt_DutyTime", acViewPreview, , "[tblDate] between #" & Me.txtStartDate & "# and # " & Me.txtEndDate & "#"
       
        DoCmd.Maximize
Else
    'Filters Report by selected base and selected date range
    
        On Error GoTo Err_runQuery_Click
    
        'DoCmd.RunCommand acCmdSaveRecord
        DoCmd.OpenReport "rpt_DutyTime", acViewPreview, , "[tblBases] = '" & mybase & "' and [tblDate] between #" & Me.txtStartDate & "# and # " & Me.txtEndDate & "#"
        
        DoCmd.Maximize
End If
Exit_runQuery_Click:
    Exit Sub
    
Err_runQuery_Click:
    MsgBox Err.Description
    Resume Exit_runQuery_Click
End Sub

 

[Wiz47]

Attached is a small example of a form that does a search and then a report based on that search. If you want to add a date range, just use two unbound textboxes (named something like txtBeginDate and txtEndDate) and add the following to the WHERE statement in the query that runs the Search Form and Report.

AND [yourDatefieldfromtable] Between forms!yourformname!txtbeginDate and forms!yourFormname!txtEndDate

 

[steve711]

Thanks for the help this is what I finally came up with from your push.

SELECT DISTINCTROW tblEntry.tblPilot, tblEntry.tblBase, Sum(tblEntry.tblRevenue_Hours) AS [Sum Of tblRevenue_Hours], Count(*) AS [Count Of tblEntry], tblEntry.tblDate, tblEntry.tblTime_In, tblEntry.tblTime_out, Sum(DateDiff("n",[tblTime_In],[tblTime_Out])/60) AS DutyPeriod
FROM tblEntry
WHERE ((([tblBase] Like [Forms]![frm_DutyTime]![lstBases] Or [forms]![frm_DutyTime]![lstBases] Is Null)=True) AND (([tblDate] Between [forms]![frm_DutyTime]![txtStartDate] And [forms]![frm_DutyTime]![txtEndDate])=True))
GROUP BY tblEntry.tblPilot, tblEntry.tblBase, tblEntry.tblDate, tblEntry.tblTime_In, tblEntry.tblTime_out;

 

[Wiz47]

In your example it appears that your filtering is happening at the query and not being passed to the query from the form as mine was attempting to do.

Would that be an accurate statement?

No,

The search form is supplying the information to the query. The query looks at the search form and filters according to what is selected in the combo boxes. If nothing is selected, it will return all the records - if one or more combos are not null - it will filter according to those selections. The more combos used, the tighter the filter. The report uses the same query and filters the report in the same manner.

 

[steve711]

Sorry I edited my post as I answered my own question before your reply. Anyway thanks again for the help.

 

[Wiz47]

Sorry I edited my post as I answered my own question before your reply. Anyway thanks again for the help.

You're welcome.

And your query looks like it will work just fine. Personally, I like this style of query because you can filter as much or as little as you want using the combos.