Filtering A Query Based Report By A Date Range

[HighXplosive]

I have a set of reports:

- Each report contains subreports.

- All reports and subreports contain listboxes.

- All listboxes are bound to various queries.

- All queries reference directly to the database, not derived tables.

- All sub-reports are bound to a query.

- All parent reports are NOT bound to any "RecordSource".

I need to:

- Allow a user to input 2 dates.

- Limit the data that is used by the queries embedded in above said reports to those within the 2 dates.

- The date is held in a table called Case: Case is not an isolated table, it has various relationships of differing degrees.

I have constructed an expression to 'limit' the range of data that should be examined by these reports and their embedded queries:

WHERE (((Case.OpenDate) Between forms!InputDateRange!FromDate And forms!InputDateRange!ToDate));

It fails to work:

- in a Macro WHERE condition as part of the OpenReport command because logically, the queries that are embedded bypass this condition and extract the data from the database direct.

- in a Query that is executed prior to the report being opened, logically because of the same reason as above.

The expression works when it is inserted into the embedded queries themselves: meaning the queries act on the expression embedded inside them with the input drawn from the preceding "InputDateRange" form.

The problem with this is that I have already constructed most of the queries with the intention to "filter out" records by the date range, THEN have the queries apply. I did not intend to filter out the records by embedding the above said expression into each and every query as:

- it would require too much time.
- it would cause further unnecessary complexity.

Therefore, I am looking for a solution to this that will, logically, impose the date range before applying the queries embedded to each of the controls in the reports. The key issue is that the queries embedded query the database directly, not a derived table or set of tables.

 

[RCheesley]

I think I have a similar problem to this:

I have four queries:

1. Queries tbl_sampleinfo to find pre-48h samples
2. Queries tbl_sampleinfo to find acute samples (taken 48h+ post admission)
3. Queries tbl_sampleinfo to find community onset community associated (sample date more than 8wks since last hospital discharge and NOT currently admitted to hospital)
4. Queries tbl_sampleinfo to find indeterminate (between 4wks-8wks since last discharge and NOT currently admitted to hospital)

I've made reports based on these queries, which are awesome. I've got a radio-button select box with a button which runs a macro to print-preview the appropriate report.

What I need to do is to prompt the user for the date range they wish to query - i.e. I want to know all CO-HA cases between 15/10/2008 and 15/11/2008.

I would ideally like this to be a popup box with text field for them to enter, with format being date (e.g. __/__/____).

Any ideas? (and I have done quite a bit of searching AND read a textbook!)

Ruth

 

[duecesup]

Go into the query design and under the criteria for your date field you need a user date response type in
Between [Start Date] And [End Date].


For isolating CO-HA you can enter the following into the Criteria for the table that houses that information

Like "CO-HA"

 

[RCheesley]

Thanks for that, something so simple I somehow overlooked :|

I've already written the queries for each type, because I want the system to determine the status from the dates entered, rather than the user .... eliminates the sleepy-tired-data-entry-person-error (i.e. me on a monday morning!)

Ruth

 

[RCheesley]

A quick addition to this, now I've got the queries working perfectly and outputting to a report, is there any way to add in the label I have for the report, the information that is filtered upon (e.g. start/end date, location, status)?

Ruth