Parametery Query

[T. T. T]

I have built a report based on a parameter query. I have a field in my
report that is a calculation( Availiable Spend: Format([Gross SqF]*2) ).
Instead of multiplying by 2 I want to use a different value each time I run
the report. So, I would like to specify the constant. Can I enter a
parameter that asks for the constant in the above calculation? If so, how?
Thanks.

 

[Bob Barrows]

I have built a report based on a parameter query. I have a field in
my report that is a calculation( Availiable Spend: Format([Gross
SqF]*2) ). Instead of multiplying by 2 I want to use a different
value each time I run the report. So, I would like to specify the
constant. Can I enter a parameter that asks for the constant in the
above calculation? If so, how? Thanks.

Well, the simplistic answer is: just do it:
Format([Gross SqF]*[Enter multiplier])

The problem of course is the lack of control: what if the user enters "two"?
Or misses the 5 key and hits T instead? Most experts use an unbound form to
solicit input from users and pass the values to reports, thus providing the
opportunity to validate the input before opening the report. If you select
that route, the steps would be:
- create a form in design view (without using the wizard and without
selecting a base table) - call it frmYourReportName
- place a textbox control on the form and call it txtMultiplier with a label
instructing the user to enter a number (no alpha characters).
- place a button on the form with the caption "Run Report"
- create an onclick event for the button in which you validate the entry in
the textbox and then use docmd.openreport to open the report
- amend the calculation above in the report to say:
Format([Gross SqF]*frmYourReportName!txtMultiplier)

An alternative to the above would be to create a VBA function in a module
that uses InputBox to get the input from the user, validates it and returns
the value (perhaps returning 1 if the input is not valid. If that is the
course you decide on, you would use this:

Format([Gross SqF]*YourFunctionName())

 

[Armen Stein]

Most experts use an unbound form to
solicit input from users and pass the values to reports, thus providing the
opportunity to validate the input before opening the report. ....
- place a button on the form with the caption "Run Report"
- create an onclick event for the button in which you validate the entry in
the textbox and then use docmd.openreport to open the report

Well, this expert <s> says to have the *report* call the *form*.

Have the report prompt the user for any criteria (and your special
multiplier) before it continues to run. Open a form from the report's
Open event. Open the form in Dialog mode so that the report waits for
the form to be closed or hidden before it proceeds. That way you can
collect criteria from the user and build a Where clause for the
report. It also means that you can call the report directly - you
don't need to call it from a form. And the selection form is reusable
- it can be called from multiple reports if they need the same
criteria.

I've posted examples of this technique on our J Street Downloads page
at http://ow.ly/M58Y
See "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Bob Barrows]

Well, this expert <s> says to have the *report* call the *form*.

Have the report prompt the user for any criteria (and your special
multiplier) before it continues to run. Open a form from the report's
Open event. Open the form in Dialog mode so that the report waits for
the form to be closed or hidden before it proceeds. That way you can
collect criteria from the user and build a Where clause for the
report. It also means that you can call the report directly - you
don't need to call it from a form. And the selection form is reusable
- it can be called from multiple reports if they need the same
criteria.

I've posted examples of this technique on our J Street Downloads page
at http://ow.ly/M58Y
See "Report Selection Techniques".

Thanks