Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    This is the result of your where ?


    Click image for larger version. 

Name:	rpt.PNG 
Views:	14 
Size:	10.5 KB 
ID:	42953

    Try
    Code:
            strWhere = "[fkMemberId] = " & Me!pkMemberId & " and  Forms![frmBorrowingBooks].Form.[cntBorrowedBooksSubForm].Form.txtDateReturned is Null"
            Debug.Print strWhere
            DoCmd.OpenReport "rptMemberBorrowedBooks", view:=acViewPreview, WhereCondition:=strWhere
    Do not forget to Dim strWhere

    However there is flaw in your logic.?
    You are referring to the form control value, so if the first record on the form has no DateRetuened value it works?, if not it states no data, yet subsequent record have no date returned.?

    You need to refer to the data in the record not the form controls.
    Last edited by Welshgasman; 09-09-2020 at 05:35 AM. Reason: Added corrected code

  2. #17
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I couldn't get the report to open with the correct filters but I don't know why. I couldn't get your code to compile either. I don't know if the two are related.

    I copied the report and named it "rptMemberBorrowedBooksFiltered"

    I copied the query used in the original report and called that "qryBorrowingBooksFiltered" but amended the criteria and used this as the record source for the new report.

    The button on the form now opens the new report.

    Whilst this is not ideal, it does at least work.

    Hopefully someone else will be able to help solve the original problem.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #18
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    thank you both

  4. #19
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi Welshgasman,
    You mentioned that : "You need to refer to the data in the record not the form controls."
    I modified it as follow:
    Code:
    strWHERE = "[fkMemberId] = " & Me!pkMemberId & " and  Forms![frmBorrowingBooks].Form.[frmBorrowedBooksSubForm].Form.DateReturned is Null"
    I am receiving the following error:


    Any ideas?

  5. #20
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi Welshgasman,
    You mentioned that : "You need to refer to the data in the record not the form controls."
    I modified it as follow:
    Code:
    strWHERE = "[fkMemberId] = " & Me!pkMemberId & " and  Forms![frmBorrowingBooks].Form.[frmBorrowedBooksSubForm].Form.DateReturned is Null"
    I am receiving the following error:

    Click image for larger version. 

Name:	Capture.JPG 
Views:	10 
Size:	21.2 KB 
ID:	42962
    Any ideas?

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-31-2020, 04:20 AM
  2. Replies: 10
    Last Post: 04-19-2017, 09:42 AM
  3. Replies: 2
    Last Post: 10-08-2016, 10:06 PM
  4. Replies: 8
    Last Post: 05-29-2015, 11:52 AM
  5. Replies: 1
    Last Post: 07-02-2010, 03:55 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