Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Unanswered: MS 2003: Need to print the date range in the report header

    Hi,
    I have built a query with this logic:

    SELECT Class.Class_Code AS Class, Count(User_Class.User_Id) AS [Num of Trainees]
    FROM Class INNER JOIN User_Class ON Class.ClassId = User_Class.Class_Id
    WHERE (((User_Class.Class_Date) Between [ENTER START DATE RANGE] And [ENTER END DATE RANGE]))
    GROUP BY Class.Class_Code;


    I built a report to pull the output of that query. It is working fine. But I can't get to print the START DATE RANGE and END DATE RANGE in the report header. Please help.

    Thanks
    Mo

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't access what you've entered for those parameters easily. Personally, I dislike the way Access lets you blur the lines between the GUI and data access in this way. It is possible but I prefer another means.

    I assume you run this report from a form. I recommend (and this will roughly be how almost everyone here does this) that you have two text boxes - one for the start date and one for the end date.
    Your query becomes:
    Code:
    SELECT Class.Class_Code AS Class, Count(User_Class.User_Id) AS [Num of Trainees]
      FROM Class INNER JOIN User_Class ON Class.ClassId = User_Class.Class_Id
      WHERE User_Class.Class_Date Between Forms!MyForm!StartDateTextbox And Forms!MyForm!EndDateTextbox
      GROUP BY Class.Class_Code
    I would go further too and have your query as:
    Code:
    SELECT Class.Class_Code AS Class, Count(User_Class.User_Id) AS [Num of Trainees], Forms!MyForm!StartDateTextbox AS start_date, Forms!MyForm!EndDateTextbox AS end_date
     FROM Class INNER JOIN User_Class ON Class.ClassId = User_Class.Class_Id
     WHERE User_Class.Class_Date Between Forms!MyForm!StartDateTextbox And Forms!MyForm!EndDateTextbox
     GROUP BY Class.Class_Code
    and show those new columns on your report.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    I am actually running the query from the report. I changed the query logic as such:
    SELECT Class.Class_Code AS Class, Count(User_Class.User_Id) AS [Num of Trainees]
    FROM Class INNER JOIN User_Class ON Class.ClassId = User_Class.Class_Id
    WHERE (((User_Class.Class_Date) Between [reports]![Class_Stats_By_Date_Range]![txtStartDate] And [reports]![Class_Stats_By_Date_Range]![txtEndDate]))
    GROUP BY Class.Class_Code;

    The query runs fine but the prompt does not pop up when I run the report. The report opens but without data.
    Mo

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is where you run the report from that is important. Do you click a button on a form to run it or just run the report from the database window?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    PARAMETERS [Enter a start date] DateTime, [Enter an end date] DateTime;
    SELECT Class.Class_Code AS Class, Count(User_Class.User_Id) AS [Num of Trainees], [Enter a start date] AS start_date, [Enter an end date] AS end_date
      FROM Class INNER JOIN User_Class ON Class.ClassId = User_Class.Class_Id
      WHERE User_Class.Class_Date Between [Enter a start date] And [Enter an end date]
      GROUP BY Class.Class_Code
    If you want to keep your set up as it is, use the above and set the control source of your header textboxes to the two new columns.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I prefer using forms myself, but for the record this would work as the control source of a textbox on the report:

    ="Between " & [ENTER START DATE RANGE] & " And " & [ENTER END DATE RANGE]

    Be aware that the bracketed text must exactly match that used in the query or you'll get prompted again.
    Paul

Posting Permissions

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