Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-24-2019, 11:07 AM   #1
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 72
Thanks: 23
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Filtering two queries one report

Hi all, I need some help, please!
I have report (rpt_Inspections) with two subreports (subrpt_AnotherTask and subrpt_Inspections), which are from queries. I have filter form, with date from and date to and cbo.What should I change in this code to filter both subforms with the filter form?


Code:
Private Sub cmd_Report_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "rpt_Inspections"      'Put your report name in these quotes.
    strDateField = "[DateOfInspection]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txt_DateFrom) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txt_DateFrom, strcJetDate) & ")"
    End If
    If IsDate(Me.txt_DateTo) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txt_DateTo + 1, strcJetDate) & ")"
    End If
        
  If Trim(Me.cbo_Worker & "") <> "" Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Worker] = '" & Me.cbo_Worker & "'"
  
    End If
    
    ' check if the strWhere has some value
    If Trim(strWhere) = "" Then strWhere = "(1=1)"
        
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub
Attached Files
File Type: zip Database_EN.zip (234.7 KB, 11 views)


Last edited by Uncle Gizmo; 10-24-2019 at 02:47 PM. Reason: Replaced Quote Tags With Code Tags
tihmir is online now   Reply With Quote
Old 10-24-2019, 03:17 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 573 Times in 569 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Two queries one report

Options:

1. main report bound to tblInspections and apply filter, subreport Master/Child links synchronize records

3. code behind report sets its own Filter and FilterOn properties

3. dynamic parameterized query as subreport RecordSource

4. button code sets subreport Filter and FilterOn properties, however, strWhere will not work for Tasks because date field has different name, will have to modify code to deal with that
Code:
    DoCmd.OpenReport strReport, lngView, , strWhere
    Reports(strReport).subrpt_Inspections.Report.Filter = strWhere
    Reports(strReport).subrpt_Inspections.Report.FilterOn = True
    Reports(strReport).subrpt_AnotherTask.Report.Filter = strWhere
    Reports(strReport).subrpt_AnotherTask.Report.FilterOn = True
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
tihmir (10-24-2019)
Old 10-24-2019, 08:58 PM   #3
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 72
Thanks: 23
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: Two queries one report

Quote:
4. button code sets subreport Filter and FilterOn properties, however, strWhere will not work for Tasks because date field has different name, will have to modify code to deal with that
I changed the fields name (date names) with the same names in both tables as you said! I added the code you wrote for me......

Quote:
DoCmd.OpenReport strReport, lngView, , strWhere Reports(strReport).subrpt_Inspections.Report.Filte r = strWhere Reports(strReport).subrpt_Inspections.Report.Filte rOn = True Reports(strReport).subrpt_AnotherTask.Report.Filte r = strWhere Reports(strReport).subrpt_AnotherTask.Report.Filte rOn = True
.......and everything works exactly as I want!Тhank you very much, June7 !!!

tihmir is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Utilisation report - multiple queries into one report Gorf Queries 8 09-09-2014 06:31 AM
Using Different Queries Same Report jadefury Reports 2 01-08-2010 11:52 AM
Three Queries One Report fenhow Reports 1 04-15-2008 07:31 PM
Using Two Queries on One Report cdogstu99 Reports 9 02-26-2007 06:47 AM
3 Queries 1 Report ? MS Access Noob Reports 2 06-16-2006 03:01 AM




All times are GMT -8. The time now is 06:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World