Results 1 to 11 of 11

Filter Criteria for Subreports of A Report

  1. #1
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87

    Filter Criteria for Subreports of A Report

    I have a report that is made up of two subreports, and I am trying to set the filter based on a combo box selection and date range. Here is what I have so far:

    Code:
    Report.[Shipping Without WOTracking].Filter = "WOTracking.ContractCo = '" & Me.cbxShipCO & "' AND WOTracking.Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND WOTracking.Date_Time <= #" & Format(dt2, "yyyy-mm-dd") & "#;"
    Report.[Shipping Without WOTracking].Filter = True
    DoCmd.OpenReport "Shipping Without WOTracking", acViewPreview
    It is throwing an "Object Required" error on the filter line, but I am not sure why.

    The SQL Source for the report returns all records is:
    Code:
    SELECT Shipping.OrderNo, Shipping.ShipDate, WOTracking.ContractCo
    FROM Shipping INNER JOIN WOTracking ON Shipping.[OrderNo] = WOTracking.[OrderNo]
    WHERE (((WOTracking.OrderNo) Is Null));
    Any assistance would be greatly appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    The code is in the main form? Try

    Me.[Shipping Without WOTracking].Filter = ...

    By the way, the line after that should probably be FilterOn, not Filter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    OK, with the change I am getting Run-time error '2465' "can't find the field '|1' referred to in your expression".

    For a little clarification, this subreport looks for orders that were shipped but did not have a corresponding WO logged. The other subreport looks for orders that have a WO logged, but were not logged for shipping. I created each report individually, then placed them as sub reports on final report. I am not sure if I went about this correctly.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    The fields listed in the filter are different than the fields in the SQL you posted. Is that appropriate?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    Corrected to:

    Code:
    SELECT Shipping.OrderNo, Shipping.ShipDate, WOTracking.ContractCo
    FROM Shipping INNER JOIN WOTracking ON Shipping.[OrderNo] = WOTracking.[OrderNo]
    WHERE (((WOTracking.OrderNo) Is Null));

    Code:
    Me.[Shipping Without WOTracking].Filter = "WOTracking.ContractCo = '" & Me.cbxShipCO & "' AND Shipping.ShipDate >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Shipping.ShipDate <= #" & Format(dt2, "yyyy-mm-dd") & "#;"
    Same error (Run-time error '2465').

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    dt1 and dt2 are declared and set first? Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    I had to make the two tables internal again. They were stored in the be. I have uploaded the db as an attachment, but it does not appear in the list. Am I doing something wrong? I tried both as a file and as a zipped folder.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    Was the zipped file under the size limit (I think 2MB)? You might try a different browser if so, I've heard of problems with IE sometimes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    Too many records, forms, etc. This one has the min. to test it. On frmGenReports, set date range (available April - Current), on Work Order - Shipping Check set Contract Company to VendorJump, then Go.
    Attached Files Attached Files

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    I'm confused. This code:

    Me.[Shipping Without WOTracking].Filter = ...

    is trying to set the filter for a subform that doesn't exist on this form. Did you mean to open the report by that name?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    I wonder if this is what you want:

    Code:
    DoCmd.OpenReport "Shipping Without WOTracking", acViewPreview, , "WOTracking.ContractCo = '" & Me.cbxShipCO & "' AND Shipping.ShipDate >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Shipping.ShipDate <= #" & Format(dt2, "yyyy-mm-dd") & "#"
    though with the test data that only works if I take the criteria out of the source query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 15
    Last Post: 10-25-2016, 06:04 AM
  2. Replies: 4
    Last Post: 10-13-2014, 02:18 AM
  3. Replies: 7
    Last Post: 09-27-2014, 09:11 PM
  4. Replies: 1
    Last Post: 10-16-2013, 03:44 PM
  5. Replies: 6
    Last Post: 05-15-2013, 03:36 PM

Posting Permissions

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