Results 1 to 4 of 4
  1. #1
    BRM1 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    2

    Report filtering

    I was given a database and asked to create a report on 3 business segments. Then edit the code within the module to generate an individual report for each of the 3 business segments.

    Issue: I have edited the module and it produces 3 reports, however the data is not filtering by each business sement, each of the 3 reports contains the data for all 3 business segments. Below is the code I am working with:

    Function CreateReports()
    Dim db As DAO.Database, db1 As DAO.Database
    Dim rcs As DAO.Recordset, rcs1 As DAO.Recordset
    Dim num, num1

    Set Application.Printer = Application.Printers("Adobe PDF")
    Set db = CurrentDb
    Set rcs = db.OpenRecordset("tblDivision", DB_OPEN_DYNASET)
    Do Until rcs.EOF
    num = rcs![division]
    report_name = "division_" & rcs![division]





    DoCmd.OpenReport "Report1", acViewDesign, "[Division]=" & num, acHidden
    Reports("Report1").Caption = report_name1
    Reports("Report1").Filter = "[Division] = " & Chr(34) & num & Chr(34)
    DoCmd.Close acReport, "Report1", acSaveYes


    DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, "C:\Test Exercise\division_" & rcs![division] & ".pdf", False
    Set Application.Printer = Nothing


    rcs.MoveNext
    Loop
    End Function

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    My guess is your [Division] field is a string. Try:
    DoCmd.OpenReport "Report1", acViewDesign, "[Division]='" & num & "'", acHidden
    BTW: Dim num, num1 creates two Variant type variables.

  3. #3
    BRM1 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    2
    RualGuy,

    Thanks for the suggestion, however the individual reports still contain the data for all business segments. Any other suggestons?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You have your WhereCondition argument in the FilterName parameter slot. Make it the 4th argument instead of the 3rd.
    expression
    .OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering Report
    By adams.bria in forum Access
    Replies: 1
    Last Post: 02-01-2012, 11:10 AM
  2. Filtering a report
    By tarhim47 in forum Reports
    Replies: 2
    Last Post: 03-30-2011, 11:49 AM
  3. Filtering report
    By huv123 in forum Reports
    Replies: 2
    Last Post: 02-15-2011, 02:59 PM
  4. Filtering a Report
    By musicalogist in forum Reports
    Replies: 12
    Last Post: 04-29-2010, 08:36 PM
  5. Filtering my report
    By ldarley in forum Reports
    Replies: 1
    Last Post: 09-05-2008, 09:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums