macros to input query/report criteria pramater values

[Guest]

I have a set of reports/queries. There are date ranges on most of these.
These are used interactively to get reports between date ranges. I can set
the criteria to do the last month or week (and have a macro run and output
the data to excel for example), but then I can't interactively have it prompt
for another date range . I don't want to duplicate reports and queries as
that means multiple objects to maintain and update.

 

[Guest]

Alan,

The best way is to build a form that the user can use to enter the 2 dates.
Use text boxes (TBox_BeginDate and TBox_End Date). The user can enter a date
then press a button to run the report.


The on- click of the print button should look something like this:

DoCmd.OpenReport strRptName, acPreview, , "[ReportDateColumnName] between #"
& Me.TBox_BeginDate & "# and #" & Me.TBox_EndDate & "#"


The next evolution of the form would be to have an option box that lets the
user select from a list of reports

Good Luck,

Stewart Rogers

 

[Guest]

Thanks,. but I already have the user part working via criteria parameters.
Don't see how doing this as a form helps or changes anything. Want to access
same report from a macro on a regular basis. I can replace the crtieria to
automatically get say last month's fisrt and last day but then a user cannot
use the same report to just get a different ad-hoc date range.

i.e. I simply want to add a macro than gets last months range "onto" of what
I have. So how does a macro then feed the form procedurally?

Alan

Alan,

The best way is to build a form that the user can use to enter the 2 dates.
Use text boxes (TBox_BeginDate and TBox_End Date). The user can enter a date
then press a button to run the report.


The on- click of the print button should look something like this:

DoCmd.OpenReport strRptName, acPreview, , "[ReportDateColumnName] between #"
& Me.TBox_BeginDate & "# and #" & Me.TBox_EndDate & "#"


The next evolution of the form would be to have an option box that lets the
user select from a list of reports

Good Luck,

Stewart Rogers

I have a set of reports/queries. There are date ranges on most of these.
These are used interactively to get reports between date ranges. I can set
the criteria to do the last month or week (and have a macro run and output
the data to excel for example), but then I can't interactively have it prompt
for another date range . I don't want to duplicate reports and queries as
that means multiple objects to maintain and update.

 

[Steve Schapel]

Alan

It sounds like your report is based on a Parameter Query, and the user
is prompted for the data range when the report is run. Is that right?
Therefore it is hard to understand "...but then a user cannot use the
same report to just get a different ad-hoc date range". Doesn't the
date range prompt happen every time the report is run. Can you please
give a bit more information?

 

[Guest]

Yes the original is set up to prompt, but I want to add a marcro that runs
the same report (which runs the query with the params in the criteria) on a
regular basis with the start and end dates automatically calculated (e.g 1st
and last days of last month). I have made a copy of the db and linked in the
source tables and modified my criteria for the expression to get 1st and last
days, but now I have to maintain 2 sets of virually identical queries and
reports. Would like to simply add the macro that passes values to the
criteria and get back to the one db.

Alan

 

[Steve Schapel]

Alan,

Thanks for the further explanation.

Here's how I would go about it... Set the Record Source of the report
to be the query with no criteria entered, and then use the Where
Condition argument of the OpenReport action in your macro(s) to restrict
the report's data according to the dates. This would involve triggering
the printing of the report from an event on a form. For example, you
could do it like this... Put an unbound Option Group on a form, with an
option for each pre-determined date range you need, for example "last
month", and another for "custom dates". If the user selects custom
dates, a couple of unbound textboxes become visible on the form to enter
the required date range. Then you have a command button with a macro on
the Click event to print the report. In the macro, use an OpenReport
action to correspond with each Option Group option, defined by a
Condition in the macro design. For example...

Condition: [YourOptionGroup]=1 '(last month)
Action: OpenReport
Report Name: YourReport
Where Condition: [YourDateField] Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)

Condition: [YourOptionGroup]=3 '(custom)
Action: OpenReport
Report Name: YourReport
Where Condition: [YourDateField] Between [Forms]![YourForm]![DateFrom]
And [Forms]![YourForm]![DateTo]

 

[Guest]

Steve Thanks. I get the idea. Only thing is the OpenReport does not allow me
to export to excel. So can I just put an OutputTo action on that report? This
is all being batch run.

This is where I was having the problem. As I don't want to open the report
but to OutputTo excel and the OutputTo does not have the Where Condition.

Alan

Alan,

Thanks for the further explanation.

Here's how I would go about it... Set the Record Source of the report
to be the query with no criteria entered, and then use the Where
Condition argument of the OpenReport action in your macro(s) to restrict
the report's data according to the dates. This would involve triggering
the printing of the report from an event on a form. For example, you
could do it like this... Put an unbound Option Group on a form, with an
option for each pre-determined date range you need, for example "last
month", and another for "custom dates". If the user selects custom
dates, a couple of unbound textboxes become visible on the form to enter
the required date range. Then you have a command button with a macro on
the Click event to print the report. In the macro, use an OpenReport
action to correspond with each Option Group option, defined by a
Condition in the macro design. For example...

Condition: [YourOptionGroup]=1 '(last month)
Action: OpenReport
Report Name: YourReport
Where Condition: [YourDateField] Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)

Condition: [YourOptionGroup]=3 '(custom)
Action: OpenReport
Report Name: YourReport
Where Condition: [YourDateField] Between [Forms]![YourForm]![DateFrom]
And [Forms]![YourForm]![DateTo]

--
Steve Schapel, Microsoft Access MVP

Yes the original is set up to prompt, but I want to add a marcro that runs
the same report (which runs the query with the params in the criteria) on a
regular basis with the start and end dates automatically calculated (e.g 1st
and last days of last month). I have made a copy of the db and linked in the
source tables and modified my criteria for the expression to get 1st and last
days, but now I have to maintain 2 sets of virually identical queries and
reports. Would like to simply add the macro that passes values to the
criteria and get back to the one db.

Alan

:

 

[Steve Schapel]

Alan,

Export a report to Excel? Interesting!

Ok, modification of my earlier suggestion... Base the report on the
query, as before, but enter the criteria for the date range into the
query design, referring to the textboxes on the form, probably be
similar to this...
Between [Forms]![YourForm]![DateFrom] And [Forms]![YourForm]![DateTo]

Then, if you have Option Group, or Command Buttons on the form for the
pre-determined date ranges, you could use VBA code, or macros, to assign
the applicable values to the textboxes, so that the query still reads
the criteria from the textboxes. Hope you understand what I mean. So,
to use the same example as before, if you use the After Update event of
an Option group, or the Click event of a Commmand Button, and you select
the Last Month option, you could have a macro using two SetValue
actions, the first one...
Item: [DateFrom]
Expression: DateSerial(Year(Date()),Month(Date())-1,1)

.... and the second one...
Item: [DateTo]
Expression: DateSerial(Year(Date()),Month(Date()),0)