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

    Open filtered report

    For a user to access reports in my db they must select the report and date parameters from the report form filter. In the report form filter there is a combo box where the user can select between reports "Type A" , "Type B", and "Type C".

    When the run button is depressed the code runs as such:

    If cboReportType =1 Then
    DoCmd.OpenReport "Total", acViewPreview, , "Type = 'A'"

    ElseIF cboReportType =2 Then
    DoCmd.OpenReport "Total", acViewPreview, , "Type = 'B'"

    ElseIf cboReportType =3 Then
    DoCmd.OpenReport "Total", acViewPreview, , "Type = 'C'"
    End If



    The report opens with the query qryTestTakers and filters correctly. There is also a Students field on the report that is =DCount("StudentID", "qryStudents"). Both the qryTestTakers and qryStudents contain the StudentID and Type.

    I realized that while the report is filtered and displays the proper records the Students field is not filtered and retruns all the records, not just those relative to the type I selected.

    Is there a code I can place into the report to filter the Students field to be filtered according to the filter of the report?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can use code to build a filter string with multiple criteria.

    Why does the combobox have 1, 2, 3 as values if you want to filter on "A", "B", "C"? Post the combobox RowSource.

    Dim strSQL As String
    If Not IsNull(Me.cboReportType) Then
    strSQL = "Type='" & Me.cboReportType & "'"
    End If
    If Not IsNull(Me.cboStudentID) Then
    strSQL = strSQL & IIf(strSQL <> "", " AND ", "") & "StudentID=" & Me.cboStudentID
    End If
    DoCmd.OpenReport "Total", acViewPreview, , strSQL

    Oooops! I just realized it must be the DCount you want to filter. Couldn't you just do =Count(*) in a textbox and get the correct result?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    2013user is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    4
    The Type combo box is on the form filter. Its souce is a table listing the Reprot types as is bound by the auto ID. That is why the code looks for 1, 2, 3,etc.

    The report is souced by qryTestTakers and the Students field is sourced by qryStudents. Count* will retrieve the number of the records on the report but has no reference to the qryStudents.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then why not apply filter that uses 1, 2, 3? Why filtering on A, B, C? Is the ID value in the report RecordSource? Why even bother with the auto ID? Just use the Type field as primary/foreign key - no lookup required. Avoid lookups whenever possible.

    What do you want to count - the number of test takers? Then shouldn't Count(*) accomplish that?

    Can apply a filter criteria to domain aggregate functions.

    =DCount("StudentID", "qryStudents","Type='" & [Type] & "'")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-06-2012, 03:25 PM
  2. Open Report filtered by Date in ListBox
    By TinaCa in forum Programming
    Replies: 1
    Last Post: 03-06-2012, 02:29 PM
  3. Open a report based on a filtered list box
    By irish634 in forum Access
    Replies: 5
    Last Post: 02-07-2012, 08:10 AM
  4. Open form to filtered records
    By ducecoop in forum Access
    Replies: 3
    Last Post: 10-22-2010, 10:53 AM
  5. Filtered Report
    By Desstro in forum Reports
    Replies: 3
    Last Post: 06-18-2010, 09:09 AM

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