Query on Subreport to pull info from Date Range on Main Report

[Sinfathisar]

Hi all,

I have a report with one subreport. The main report has 2 controls (one for Start_Time and the other for End_Time) and the criteria asks for Dataset_ID. I need the criteria in the subreport to pick up the Start_Time and End_Time and display records between these dates for the user input Dataset_ID in the main report. I hope what I'm trying to say makes sense...

I tried using this as the criteria in the Date control on the subreport, but it didn't seem to work.

>=[Reports]![nameofmainreport]![Start_Time].[Value] And <=[Reports]![nameofmainreport]![End_Time].[Value]


Any thoughts??

 

[dkinley]

I am not sure if this can be done or not through a report - I've always applied the criteria to the underlying query of the report.

Then, in the report - the controls with the date information are unbound and refer to the form for its data.

-dK

 

[Sinfathisar]

I need all the associated records from the subform to show up on the main report without having to force the user to enter the Start_Time and End_Time. I've tried a few more things - and now it will show just the record with the last applicable date when before it would show no records at all.

I attached a zip that contains the report, subreport, and associated tables - if anyone has time to look at it I would greatly appreciate it. I am grasping for straws and don't know what I could do make this work...it seems like there has to be a way to do it though.

Here is a discription of what I changed to create the result I am now getting:
I used the same criteria as I stated in my first posting, but I added the table used by the main report to the subreport query, linked the two tables by their Team_ID, and added the Dataset_ID from the Dataset_Table to the subform query. (The main form query is based on the Dataset_Table its criteria is for the Dataset_ID).

 

[dkinley]

It will probably be easiest to recommend this site ... http://www.techonthenet.com/access/index.php

You can look at the reports and see how they do it there. They also do it for queries in that section.

-dK

 

[Sinfathisar]

It will probably be easiest to recommend this site ... http://www.techonthenet.com/access/index.php

-dK

Thanks anyway dkinley - it doesn't seem like any of the examples on that website are doing what I am looking for...

 

[rapsr59]

Hi,

Can you show the query that the main report is based on? (the record source for the report.)


Richard

 

[Sinfathisar]

Hi,

Can you show the query that the main report is based on? (the record source for the report.)

Hi,

It wasn't showing anything when I viewed the query for the main report - but I did manage to get the thing to work.

I had a coworker copy my report to his pc and it worked - we were puzzled for a couple of days trying to figure out why and finally realized that the problem was related to date formatting issues. The Start_Time and End_Time controls are long dates (and need to be long dates) and the Date control on the subform is a short date (and needs to be). We ended up altering the original criteria I had posted to read from the Dataset_Table (table) instead of the related Collection report (my main report) controls, and then used the INT function to read the Start_Time and End_Time as short dates.

Here is my final working criteria:
Between INT([Dataset_Table]![Start_Time]) and INT([Dataset_Table]![End_Time])+0.99
The 0.99 makes sure that you don't lose the last daily log record you need and also makes sure you don't include the daily log record with the next date (one day past what you want to include).

We also set the Team_ID on the subform as the master and child fields so we don't include daily logs from teams other than the what we want to display for each dataset ID.

I will post up another zip with my reports and related tables shortly...

 

[Sinfathisar]

Here is my report with a subreport. You enter the dataset Id in the main report and it automatically pulls up the daily logs within the date range of the dataset Id.

If anyone is interested to see how it works...