I have 6 reports that i want to place in a sinlge report. Each of the reports source is a query from a date range. When place thw 6 subrepoert in one report I get a prompt for the start and end date 6 times (i'm using the Between[start date] and [end date] criteria in the query. I would like a way to have the report open and only have to put the date once. Is this possibe?
Subreport date prompt
- Thread starter Vargasman
- Start date
This is what worked for me...
Create a form (call it SelectDates) and put 2 unbound text boxes on it. Call them txtStart and txtEnd. Then go to each of your queries and put the following as the criteria for your date fields:
BETWEEN Forms![SelectDates]![txtStart] AND Forms![SelectDates]![txtEnd].
Next put a Command button on your form to run your report. In the report put 2 unbound text boxes where you want the dates to appear. I usually put them in the report header. In the first box, I make the DataSource; =Forms![SelectDates]![txtStart] set the label to "From". In the second box I make the DataSource; =Forms![SelectDates]![txtEnd] and make the label "To".
Now, when you open that form, you enter the dates just once and each query pulls the dates from the form and they show up on the report.
Create a form (call it SelectDates) and put 2 unbound text boxes on it. Call them txtStart and txtEnd. Then go to each of your queries and put the following as the criteria for your date fields:
BETWEEN Forms![SelectDates]![txtStart] AND Forms![SelectDates]![txtEnd].
Next put a Command button on your form to run your report. In the report put 2 unbound text boxes where you want the dates to appear. I usually put them in the report header. In the first box, I make the DataSource; =Forms![SelectDates]![txtStart] set the label to "From". In the second box I make the DataSource; =Forms![SelectDates]![txtEnd] and make the label "To".
Now, when you open that form, you enter the dates just once and each query pulls the dates from the form and they show up on the report.
Last edited:
- Local time
- Today, 16:52
- Joined
- Sep 12, 2006
- Messages
- 15,987
alternatively store the dates in global variables
public gblstartdate as date
public gblenddate as date
public function readstartdate() as date
readstartdate = gblstartdate
end function
public function readenddate() as date
readenddate = gblenddate
end function
then any time you need a date read routine in ANY form, you only have to assign the dates
as
gblstartdate = mystartdate
gblenddate = myenddate
and the queries need to have a condition
... between readstartdate() and readenddate()
one benefit here is that you can temporarily modify the global functions to test your queries, without needing to open a particular form to read the dates
public gblstartdate as date
public gblenddate as date
public function readstartdate() as date
readstartdate = gblstartdate
end function
public function readenddate() as date
readenddate = gblenddate
end function
then any time you need a date read routine in ANY form, you only have to assign the dates
as
gblstartdate = mystartdate
gblenddate = myenddate
and the queries need to have a condition
... between readstartdate() and readenddate()
one benefit here is that you can temporarily modify the global functions to test your queries, without needing to open a particular form to read the dates
A simple way is to use the following in a New Query Column (just add it at the end of your querie's normal columns & set the criteria for the new column to True;
[YourDateField] Between [Forms]![YourUnboundForm]![txtStart] And [Forms]![YourUnboundForm]![txtEnd] Or [Forms]![YourUnboundForm]![txtStart] Is Null
Obviously you will still need the two date fields on your Unbound Form ([txtStart] & [txtEnd]). If the Start field is left blank (Null) your report will show all dates but if you enter a date then the query will filter out according to your choice. It works well & is very simple to get working each time you use it. By fiddling with the code you can easily use it to show other parameters (data). Just add extra columns in the query with the edited code.
[YourDateField] Between [Forms]![YourUnboundForm]![txtStart] And [Forms]![YourUnboundForm]![txtEnd] Or [Forms]![YourUnboundForm]![txtStart] Is Null
Obviously you will still need the two date fields on your Unbound Form ([txtStart] & [txtEnd]). If the Start field is left blank (Null) your report will show all dates but if you enter a date then the query will filter out according to your choice. It works well & is very simple to get working each time you use it. By fiddling with the code you can easily use it to show other parameters (data). Just add extra columns in the query with the edited code.
Last edited:
