Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-04 : 00:22:28
|
| cuner writes "im using access 2000 and i want to create a report of db access help file does not include dynamic query how can i create it ?thanks 4 answers( sorry this is not my language :D )) " |
|
|
ewade
Starting Member
36 Posts |
Posted - 2002-02-07 : 14:07:40
|
From your question, it sounds like what you are trying to accomplish is a parameter query. A parameter query prompts the user for a value at run-time, and uses the value as a criterion when the query is executed against the data.There are two ways to handle this in Access. There's the perfectly serviceable "quick and dirty" method that results in a system dialog box popping up with a short prompt. Then there's the more elegant solution of creating an unbound form and using it to supply the parameters to the query at run-time. We'll look at both, below.The Quick-and-Dirty Solution - Create a new SELECT or CROSSTAB query
- For each field that you wish to allow users to supply a parameter, type in a short prompt enclosed within square brackets
- At run time, Access will display pop-up dialogs
 Creating a simple parameter query The results of the simple parameter queryOf course, Access isn't very intelligent, so this method will only return records with an exact match on the value your user supplies. One way to get around this is to use the LIKE operator. In the example above, my criteria was simple: [What location?] Adding the LIKE operator for maximum flexibility looks like this: LIKE "*" & [What Location?] & "*" The Elegant SolutionThe elegant solution is to create a form that supplies parameters to the query. The beauty in this solution is that you have total control over the appearance of it, and you can use different control styles to limit how the user will interact with the query (text box, combo box, chekc box, radio button, etc.).First, you must create an unbound form. A modeless popup window tends to be best, since this prevents the form from being hidden behind other windows. - Open the property sheet for the new form
- Set the PopUp property to YES
- Set the BorderStyle to THIN or NONE
- Create the form, and name each of the controls
- Create your query. For each field that you want to use a value from the form, use an Access object reference to the corresponding control on the form.
- Create a button on the new form that executes the query. I find that it's actually a bit easier if you simply create a macro or event procedure that opens the query or report, then closes the form.
 Here you can see how I've referenced the controls on my unbound formExample of Access Object Reference: [Forms]![Report1-10]![ProgramCode] I've created the form, and added two command buttons. One calls a macro, the other closes the form. The macro contains two steps: OpenQuery and Close Form. When the user clicks the "Run Query" button, it executes my macro. The macro opens the query first. As the query loads, it immediately requests the values from the form. Then the macro closes the form, leaving the query results grid open in its place.Of course, you can easily use this technique to create customizable reports- simply base your report on the query, and change the macro to open the report rather than the query. As the report opens, it will call the query automatically. If you wanted to get really fancy, you can even have the report show what parameters were used to create the data set. Simply create an unbound text field on the report, and use an Access object reference to refer to one of the fields on the unbound form.   |
 |
|
|
|
|
|
|
|
dynamic query in access