If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Parametery Query
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. |
#2
|
|||
|
|||
Parametery Query
T. T. T wrote:
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()) -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#3
|
|||
|
|||
Parametery Query
On Fri, 18 Dec 2009 07:42:41 -0500, "Bob Barrows"
wrote: 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 |
#4
|
|||
|
|||
Parametery Query
Armen Stein wrote:
On Fri, 18 Dec 2009 07:42:41 -0500, "Bob Barrows" wrote: 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". Thanks :-) -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
Thread Tools | |
Display Modes | |
|
|