Results 1 to 8 of 8
  1. #1
    NewbyForAccess is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2015
    Posts
    49

    Date Retrieval Using Start/End Dates Fields on Opening Form Containing Reports


    I'm very new at using Access so hoping someone here can help me. I have an opening form that contains command buttons to preview all the reports I've created. The reports are working fine and pulling the criteria I want on each of these reports because I have the queries pulling the data by using the Between function in the Date field. I have now also added two text boxes (one for Start Date and one for End Date) and also a command button to update the dates. I'd like for all my reports on this same opening form to preview by pulling the data using the dates typed into the Start Date and End Date fields. Can someone tell me how I can get this to work? I've researched until I'm researched out and can't find the help to achieve this. What I have found and tried wasn't working for me. Please help!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can pass criteria to a Report's Filter property when using VBA to open the report.

    Here is an example of some code that you might place in the click event for a command button.


    Code:
    Dim strWhere As String
    strWhere = "[MyDateField] BETWEEN #" & Me.TextBoxName1 & "# AND #" & Me.TextBoxName2 & "#"
    
    DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

  3. #3
    NewbyForAccess is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2015
    Posts
    49
    Thank you so much for this! I just tried it on one of the reports, and it's working great!

  4. #4
    NewbyForAccess is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2015
    Posts
    49
    Okay, this worked great for some of my reports. However, when trying to run others that were based on the same query but also pulling from another field, it hung up. Is there a code I can put in the query itself to pull the range of dates by using the same date fields that worked in this example that are on my opening form? Thanks so much for your help!

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by NewbyForAccess View Post
    ...Is there a code I can put in the query itself to pull the range of dates by using the same date fields that worked in this example that are on my opening form?...
    I am not understanding what it is you desire. You can place the name of the form and the control in the query, using the criteria field for the two date fields. As long as the form is open and has a valid date in both of the fields, the query will use the values in the form's controls as criteria.

    I do not understand what "when trying to run others that were based on the same query but also pulling from another field" is describing. As long as there is a valid date in the controls, the data types in the table's fields are of type Date, and all of the names are typed correctly, the code example provided should work. Maybe you are trying to apply criteria to an additional field/column that is not of type Date.


    The syntax is different for Dates, Numbers, Booleans, and Text.

  6. #6
    NewbyForAccess is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2015
    Posts
    49
    Sorry I have you confused. I confuse myself sometimes. I've done further texting and this is working perfectly and pulling all the correct data. What's happening is that I'm getting a syntax error when overlooking putting in the dates prior to executing a report command button. Is there a fix for this. I have error codes for the report command buttons in the case that someone would cancel it, but this error code doesn't seem to work for overlooking putting in the dates in the Start Date and End Date fields. Thanks so much for your help!

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Before you execute code that requires variables, you need to do data validation. Here is an example of checking for Null in the two textboxes that are the variables included in the OpenReport criteria.

    Code:
    If IsNull(Me.TextBoxName1) Or _
       IsNull(Me.TextBoxName2) Then
    MsgBox "Please enter Criteria"
    Exit Sub
    End If
    
    Dim strWhere As String
    strWhere = "[MyDateField] BETWEEN #" & Me.TextBoxName1 & "# AND #" & Me.TextBoxName2 & "#"
    DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

  8. #8
    NewbyForAccess is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2015
    Posts
    49
    Thank you so very, very much! I tried this and it's working exactly how I wanted. I'm so glad I found this forum. It's a wealth of information and help for newbies like me.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-02-2015, 07:25 AM
  2. Replies: 3
    Last Post: 12-06-2014, 03:59 AM
  3. List Dates between Start and Finish Dates
    By Buddus in forum Queries
    Replies: 1
    Last Post: 07-26-2013, 01:58 PM
  4. Start form on opening database
    By Ted C in forum Forms
    Replies: 2
    Last Post: 08-10-2010, 08:00 PM
  5. Replies: 1
    Last Post: 07-07-2010, 04:22 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
  •  
Other Forums: Microsoft Office Forums