Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering a Report By Date (Access 2000 -9.0 SR1)

    I have an application written by me several years ago. It collects information via a form on studies conducted, one study to a record. I set up several reports to sort and print the data collected and everything was cool! Now there are almost 1000 records in the table, and the users would like to be able to run the same reports, based on certain start and end dates (to be entered interactively each time a report is run). The date field is in the information collected of course. Without much VB knowledge, can someone recommend a direction? Is this something that can be set up using a query? Or if it does take VB, can you point me in the right direction? I am scheduled to take a class in October for VB for Access, but I hate to ask the users to wait till then. I posted this question a few minutes ago but it never showed up on the board, in case you should see two copies of the same post now! If you need more information, just say what and I can provide it. Thanks!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a Report By Date (Access 2000 -9.0 SR1)

    You can accomplish this one of two ways (others may know more), both without VB.
    First, you can based your reports on a parameter query which will display 2 parameter windows whenever the reports are run; one for Start Date and one for End Date. Look in Access Help under Parameter Queries/create a parameter query that prompts the user for criteria each time it is run.
    Second, you can base your reports on a parameter query that will display a custom dialog form whenever the reports are run; the user can enter the Start Date and End Date in a form. Look in Access Help under Parameter Queries/create a parameter query that gets criteria from a custom dialog box.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a Report By Date (Access 2000 -9.0 SR1)

    Thanks for the direction-and here's where I got to based on the information you provided. I created a query which prompts for the beginning and ending date. I'm a bit perplexed (hate to sound stupid, so I'll use a different word!) The report where I would like to use this logic now runs off of a table (named 'Measure') I checked the Access Help menu for the areas you told me, but I'm still not sure how to incorporate the new query into the report that has Measure as its record source. I could find no direction in Access help to tell me if you can use two record sources (what's the syntax?) for a report. Or do I need to re-create the report based on the new parameter query rather than the Measure table and add all the measure variables that are in the current report to the new query? Can you give me more please? A smaller problem is that the query prompts fine, but when I enter the beginning and ending date, I get an error which says ' The expression is typed incorrectly, or is too complex to be evaluated. Try simplifying the expression by assigning parts to expression to variables' which also perplexes me. As you can see, I'm perplexed! If you prefer that I take this off the boards and send future notes to your personal mail, just say so. I anticipate this may not be the last question (humor)
    Thanks. Candy

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a Report By Date (Access 2000 -9.0 SR1)

    If you want the report to run only based on the parameter query, just change the report's record source to your parameter query. If you need it to run based on tblMeasure at some times and the parameter query at other times, save the current report under a new name and change the record source to your parameter query. Then you will not have to completely recreate the report. I do not know how to choose a record source at run time (maybe someone else does).

    As for the parameter query error, I do not know what the entire query looks like, so I am not sure precisely what is causing this error. You need to post more information or a small sample db that shows the query and will create the error.

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a Report By Date (Access 2000 -9.0 SR1)

    The report currently runs on the Measure table-which has all the variables called in the report. The user needs to be able to choose the beginning and ending dates for which the report should be run (this is the intention of the query) The query is also based to the Measure table. The problem with the query has been solved. When I looked at the input format, I realized that in responding to the query prompts, I had omitted the '/' in the format (02/28/1998), so the query now returns a list of the correct dates. My problem is thus simplified: How do I hook the query up with the report, so that when the user runs the report, the query comes up prompting for the dates, and when entered, the report runs using those dates only. Thanks for your continued help..

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a Report By Date (Access 2000 -9.0 SR1)

    Place your parameter query name in the record source row for the report.
    In the Report Design view, the Record Source property is found on the data tab.

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Location
    Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a Report By Date (Access 2000 -9.0 SR1)

    My reports are running fine now; thanks to you Thomas, I learned a new skill in Access

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •