Using Parameters
Often it is not possible to know in advance the criteria for a query field. In such cases, where the filter values are not known until runtime, a variable (parameter) can be used. When these queries are run, the user is prompted for the value of each parameter. (The parameters can also be assigned programmatically). Using parameters in queries is extremely powerful and converts static "hard-coded" queries to flexible, dynamic ones. The use of parameters can significantly reduce the number of queries you need, makes queries more useful, and simplifies database maintenance.
Parameters can be added very easily. Rather than entering the value of a criteria, enter between brackets the prompt you want the user to see when the query is run. The value the user enters replaces the parameter in the query. In the following example, a parameter [Enter State Name:] is the criteria in the [State] field, and [Enter Minimum Age:] is the parameter in the [Age] field. When this query is run, the user is prompted for the state desired and minimum age, and the records matching that value are retrieved.
Parameters work provided the parameter definition does not conflict with the field name among the query's tables.
To better define a parameter, you should define it in the list of parameters. This is an optional step, but there are good reasons to do so, right mouse click on the top part of the query and choose Parameters. This form appears to let you list each parameter name and its type:
By explicitly defining parameters, users are forced to enter values conforming to the type. While it may not matter for text fields, it is useful for numeric and date fields. This will minimize data entry errors that cause a "Can't evaluate expression" error message to appear.
One of the most powerful features of Microsoft Access queries is their support for Access functions. This is most useful in Update queries, but can also be used in Select queries. The Advanced: Access Functions query is an example of this feature:
This query selects the Country names in descending order of name length. The second field renames itself to [Length], uses the LEN function to calculate the length of each country name, sorts the length in descending order, and excludes any records with 10 letters or fewer.
While this may not seem particularly useful, there are many situations where using Access functions is extremely useful and eliminates the need to program. The string functions in particular (Left$, Right$, Trim$, Mid$, UCase$, LCase$ etc.) are useful for manipulating portions of strings and changing case.
In addition to using Microsoft Access functions, queries also support user defined functions. Functions defined in Access Basic/VBA modules must return an appropriate value and can be used to manipulate each record. You can reference field values by passing the field name in brackets.
Here is an example where a function (StripLead) is used to remove the leading word of a phrase if it starts with "The", "An", or "A". This is useful for sorting phrases such as book titles on "real" words:
Ato ny tohiny
Link to FMS Technical Papers - Microsoft Access Query Tips and Techniques
VB .NET 
Page précédente 