| Null Responses... |
|
Parameter Queries - What If the User Ignores the Prompt?The idea of a parameter query is that it offers the user some choice when they run the query (see the tutorial on Using Parameter Queries). Instead of you having to anticipate the various combinations of criteria that you are going to need and creating a separate query for each, you use parameters to prompt for information when the query is run. Access does this by presenting the user with an input box into which they type what they want to see. When they click the [OK] button the query places what the user typed into the appropriate place in the query definition and runs the query. But what if the user leaves the input box empty? You might expect that if the query receives no input it would return all the records, but that isn't what happens. It returns nothing at all - an empty recordset. It's really easy to adjust your criteria so that the query will return all the records when the user ignores the prompt and doesn't type anything. They just click [OK] and if they want to see all the records. Here's how... A Regular Parameter Query Here's a regular parameter query with a prompt for the user to enter the name of the Office whose records they want to see in the query's result...
In this example the user typed Cardiff and got the following result...
If they had ignored the prompt and left the input box empty they would have seen no results at all. Giving the Option to Return All Records Suppose the user doesn't know what to type, or perhaps this time would like to see all the records? All we have to do is adjust the criteria to accept whatever the user types, or to return all the records if they type nothing (i.e. if the input is "null"). Here the criteria have been modified to accept a null entry...
...and here's what the user saw when they left the prompt box empty...
Ignoring the prompt returns all the records thanks to the modification to the criteria. To summarise... A parameter that requires an input from the user, otherwise no records are returned is written...
A parameter that can accept an input from the user, but that will return all records if no input is made is written...
As you can see, the prompt is entered twice although the user sees only one input box. It is important that the prompt is exactly the same in both cases, otherwise Access will treat them as separate parameters and the user will see two input boxes (although the query will probably still work!). Variations on a Theme If you prefer you can put the two parts of the parameter on separate lines in the query grid, like this...
Going down the column is equivalent to typing "Or" in the criteria. But I prefer to do it the other way. Doing it this way is fine if you aren't combining the parameter with criteria on other fields. If you are, then you have to be careful that your criteria read the way you intended. I find it's easier to put the whole thing on one line. TIP: Sometimes you have a lot to type in the cell of the QBE grid. You can stretch the column to fit what you type - point to the tops of the columns where they meet. When your cursor changes from an arrow to a black cross with horizontal arrows you can drag to the desired width or double-click to snap to fit. An easier way is to right-click on the cell and choose Zoom... from the shortcut menu. A large text box opens into which you can type your entry. Close the text box to put your typing into the cell. This is great for doing those fiddly corrections to existing entries. It works in tables too! Just like regular parameters, these ones can be combined into multiple parameters. For example, I could have had an additional prompt for Department which could also accept a null entry (click the thumbnail to see the full-size image)...
Now the user can specify an Office or a Department or neither or both. Now don't tell me you don't think that's clever! But what would be really useful, of course, would be a custom prompt with combo boxes for users who can't remember the names of all the offices and departments. As the man said... "You ain't seen nuthin' yet..."
|
| ^ top | |
|
|
