Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Specify Date Range in Report (Access 2K, Office 2K Pro)

    This should be easy but I can't seem to think out of this particular box...

    I have a report with two subreports and want to allow the user to view the data by Quarters and have the report *show* what the current quarter/date range has been specified. I know you can set up a query builder on the report's query/table source to prompt for Date1 and Date2, but what I haven't been able to figure out is how to capture that input and re-display it in the report.

    Ideally, I'd like the user to be able to pick a year and quarter and have the system generate a report with the selected date range and Quarter in the header information. I've put together a query that autogenerates Quarters for the current Year, which is getting closer, but I can't pass the query date ranges to another query based on the source table to spit out records for a particular quarter. I'm thinking I'll probably have to go into VBA to generate a Quarterly range by a specified year, which is fine, but then hpw to pass that info to a query as a Between Date1 and Date2?

    Also, when I just do the Date1 and Date2 using the query builder on the table I get a multi-page report for every date in the date range and that's not so great. If I reconfigure the query to group by some other information I can reduce the amount of spurious pages but really I just need the one. using SELECT DISTINCT doesn't work as a different date or any other variable will generate it's own row with DISTINCT set in the SQL.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Specify Date Range in Report (Access 2K, Office 2K Pro)

    You can use a parameter query to prompt the user for a year and quarter, but it is more elegant to use a form. You can use text boxes or combo boxes to let the user enter a year and a quarter, and calculated text boxes to get the first and last date of the selected quarter. Use these text boxes as parameters for the query and to display the dates on the report. The date text boxes can be hidden (Visible property set to No) if you like.

    I have attached a zipped Access 97 database that contains just one form frmQuarters. It does nothing, it is just meant to show how you can compute the first and last date of a quarter. You could import it into your database, put a command button on it to open a report, and use it as follows:

    In the criteria of a query:
    Between [Forms]![frmQuarters]![txtStartDate] And [Forms]![frmQuarters]![txtEndDate]

    In the Control Source of a text box on the report:
    ="Data for Quarter " & [Forms]![frmQuarters]![cbxQuarter] & " of year " & [Forms]![frmQuarter]![cbxYear]
    or
    "Data for the period between " & [Forms]![frmQuarters]![txtStartDate] & " and " & [Forms]![frmQuarters]![txtEndDate]
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Specify Date Range in Report (Access 2K, Office 2K Pro)

    You'll have to be more specific about how you want to group or reduce the data in your report.

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify Date Range in Report (Access 2K, Office 2K Pro)

    that is, in fact, exactly what i ended up doing: creating a special unbound form with the requisite text box to enter the 1st month of a quarter and a second one with the necessary text manuipolation to show the 3rd month following. That works as you can link up the form field in the BETWEEN criteria in any query you want. actually i had done this before in an old project and recalled facing a similar situation, looked up the old project... I got lost in the wilds of Expression Builder on query criterions and went so far as to write a function to produce quarter ranges on a year input, none of which I need now. learned a little, I suppose, but mostly had a platonic moment: remembering what i had forgotten. an especially 'blonde' platonic moment, indeed...

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Specify Date Range in Report (Access 2K, Office 2K Pro)

    I don't think I can get the system to do what I wanted. Basically, I set up two queries: Intakes by Quarter and Discharges by Quarter, either of which can produce a nice little report. I wanted to put them both in a container report with the two separate quiries as sub-reports and tried to set up a query using the Entry Date on the Container Report to generate the expected Sub Report totals. The problem of course is marrying two different query results (Intake and Discharge) in a new query: you'll get at least as many rows as the longest from either side. I can group by the Advocacy Group name to get a count of the number of cases by Intake or Discharge but that gives me 3 pages (as there are 3 different Advocacy Groups returned in the 'longer' query result) and, hence, 3 pages in total of what I want to display in one page.

    So I just knocked it down to 2 separate reports.

Posting Permissions

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