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_Da te] 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_Da te] 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_Da te] 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 - BFNMULTIPLEDATE PROMPTS

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

[Bradley]

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

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

"BlackFireN ova" <BFN_E-Subscriptions@m yrealbox.com> wrote in message
news:3fa89940$0 $193$75868355@n ews.frii.net...[color=blue]
> 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_Da te] 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_Da te] 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_Da te] 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 - BFNMULTIPLEDATE PROMPTS
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[NeilAnderson]

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

"Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message news:<3fa8a8ad@ nexus.comcen.co m.au>...[color=blue]
> 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
>
> "BlackFireN ova" <BFN_E-Subscriptions@m yrealbox.com> wrote in message
> news:3fa89940$0 $193$75868355@n ews.frii.net...[color=green]
> > 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_Da te] 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_Da te] 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_Da te] 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 - BFNMULTIPLEDATE PROMPTS
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it![/color][/color]

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]

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

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]

Re: OK, got the date prompt, thanks - Now a related question

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(Da te()),"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 BFNMULTIPLEDATE PROMPTS BFNMONTHNAME

rhodesar@yahoo. com (Sarah) wrote in message news:<739cac7d. 0311061306.737f 67ef@posting.go ogle.com>...[color=blue]
> 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.[/color]

[NeilAnderson]

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

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]

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

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!