How do I avoid Multiple Prompts for Date Range in a report with subreports?

[BlackFireNova]

Using Access 2002

I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan to use sub-queries and sub-reports to
filter and display the data in the unrelated tables in my report. The
common information is a user-inputed date range.

I want to avoid having the user prompted for the [Report_Start_Date] and
[Report_End_Date] variables repeatedly. Somehow I need to capture those
dates as entered, so I can refer to them for all subsequent queries and
sub-reports which are part of the complete report.

I have written one query, based on the table which contains most of the
information I need. I put a Prompt in the Date field which reads:
Between [Report_Start_Date] and [Report_End_Date].

This filters that query as desired, but where do I go from here?

How do I capture those two date variables for reference by other
objects?

Once I have done that, how do I then re-use that data to filter other
objects (unbound text boxes, queries, etc.?

Also, I would like the report to have something in the header like:
From [Report_Start_Date] to[Report_End_Date]. When I put an unbound
text box on the report, I can only see the Expression Builder as a tool
for customizing that control. What is the most optimum way to use the
expression builder to cause the control to display the desired "stored"
variables?

Please go easy on me! I'm just learning Visual Basic, and am trying
hard, but I need just a touch of explanation, and TLC if you can!

Thank you!
BlackFireNova - BFNMULTIPLEDATEPROMPTS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bradley]

Perhaps setup a basic form to enter those dates and reference the form from
your queries and report?

eg. Forms!frmMyForm![StartDate]

--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:3f*********************@news.frii.net...

Using Access 2002

I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan to use sub-queries and sub-reports to
filter and display the data in the unrelated tables in my report. The
common information is a user-inputed date range.

I want to avoid having the user prompted for the [Report_Start_Date] and
[Report_End_Date] variables repeatedly. Somehow I need to capture those
dates as entered, so I can refer to them for all subsequent queries and
sub-reports which are part of the complete report.

I have written one query, based on the table which contains most of the
information I need. I put a Prompt in the Date field which reads:
Between [Report_Start_Date] and [Report_End_Date].

This filters that query as desired, but where do I go from here?

How do I capture those two date variables for reference by other
objects?

Once I have done that, how do I then re-use that data to filter other
objects (unbound text boxes, queries, etc.?

Also, I would like the report to have something in the header like:
From [Report_Start_Date] to[Report_End_Date]. When I put an unbound
text box on the report, I can only see the Expression Builder as a tool
for customizing that control. What is the most optimum way to use the
expression builder to cause the control to display the desired "stored"
variables?

Please go easy on me! I'm just learning Visual Basic, and am trying
hard, but I need just a touch of explanation, and TLC if you can!

Thank you!
BlackFireNova - BFNMULTIPLEDATEPROMPTS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[NeilAnderson]

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message news:<3f******@nexus.comcen.com.au>...

Perhaps setup a basic form to enter those dates and reference the form from
your queries and report?

eg. Forms!frmMyForm![StartDate]

--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:3f*********************@news.frii.net...

Using Access 2002

I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan to use sub-queries and sub-reports to
filter and display the data in the unrelated tables in my report. The
common information is a user-inputed date range.

I want to avoid having the user prompted for the [Report_Start_Date] and
[Report_End_Date] variables repeatedly. Somehow I need to capture those
dates as entered, so I can refer to them for all subsequent queries and
sub-reports which are part of the complete report.

I have written one query, based on the table which contains most of the
information I need. I put a Prompt in the Date field which reads:
Between [Report_Start_Date] and [Report_End_Date].

This filters that query as desired, but where do I go from here?

How do I capture those two date variables for reference by other
objects?

Once I have done that, how do I then re-use that data to filter other
objects (unbound text boxes, queries, etc.?

Also, I would like the report to have something in the header like:
From [Report_Start_Date] to[Report_End_Date]. When I put an unbound
text box on the report, I can only see the Expression Builder as a tool
for customizing that control. What is the most optimum way to use the
expression builder to cause the control to display the desired "stored"
variables?

Please go easy on me! I'm just learning Visual Basic, and am trying
hard, but I need just a touch of explanation, and TLC if you can!

Thank you!
BlackFireNova - BFNMULTIPLEDATEPROMPTS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I had a similar problem. I used a custom dialog box which made one of
the sub queries in code. The query had 2 expressions StartDate &
EndDate that could be used as fields in the report's header.
I've only been working with access for a couple of months & I'm a
total amateur so this may not be a good way to do this, but it does
work.

[Sarah]

I do this quite often and use a create table or append query to add
the date range into a table. You can then reference this table in all
subsequent queries, which means you'll only get prompted once. You
can also reference these dates in your report.

For example, I would set up a table called tbl_date with the two
fields in start_date and end_date, and create an append query to add
the dates to the table. This means before you run the append query
you will have to run a delete query to delete any dates that were in
there previously.

Hope this helps.

[BlackFireNova]

Thanks to all of you for the help with the start / end date problem.
I used a form as suggested and referenced it, and it worked great.

On that form I also decided to include some fill-in fields for the
header of the report. One of the fields I wanted was to simply show
the current month, in TEXT.

I created an unbound text box and put in the following:
Month(Date()) This gave me the NUMERIC month.

I then tried : Format(Month(Date()),"mmmm"), thinking OK, this would
take the numeric month, and turn it into the TEXT Month Name, but for
some reason it returns JANUARY (instead of November, which the Date()
right now would come up with 11.

How can I modify this or what command can I use to take the Date() and
extract the month (11), and then turn it into the TEXT Month
(November)?

BlackFireNova BFNMULTIPLEDATEPROMPTS BFNMONTHNAME

rh******@yahoo.com (Sarah) wrote in message news:<73**************************@posting.google. com>...

I do this quite often and use a create table or append query to add
the date range into a table. You can then reference this table in all
subsequent queries, which means you'll only get prompted once. You
can also reference these dates in your report.

For example, I would set up a table called tbl_date with the two
fields in start_date and end_date, and create an append query to add
the dates to the table. This means before you run the append query
you will have to run a delete query to delete any dates that were in
there previously.

Hope this helps.

[NeilAnderson]

I've done the table thing elsewhere in my database and it works well.
I hadn't thought of doing it for the report. I wonder if one method is
faster than the other? My report take a while to load but I'd assumed
that this was due to the amount of calculations that it was doing, but
now I'm not so sure. Anyone Know?

[BlackFireNova]

Thanks for the suggestion about making up the table, and the delete
query. It worked great!

Much appreciation to everyone who helped!

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!