Microsoft KB Archive/95931: Difference between revisions
(importing KB archive) |
m (Text replacement - "&" to "&") |
||
(2 intermediate revisions by the same user not shown) | |||
Line 116: | Line 116: | ||
<pre class="fixed_text"> Customer ID Employee ID Result | <pre class="fixed_text"> Customer ID Employee ID Result | ||
----------------------------------------------------------------- | ----------------------------------------------------------------- | ||
<blank> <blank> All 830 orders in Microsoft Access 97 | |||
and 7.0; 1078 orders in earlier versions. | and 7.0; 1078 orders in earlier versions. | ||
AROUT | AROUT <blank> 13 orders for AROUT in Microsoft Access 97 | ||
and 7.0; 14 orders in earlier versions. | and 7.0; 14 orders in earlier versions. | ||
AROUT 4 4 AROUT orders for employee 4. | AROUT 4 4 AROUT orders for employee 4. | ||
<blank> 4 156 orders for employee 4 in Microsoft | |||
Access 97 and 7.0; 209 in earlier | Access 97 and 7.0; 209 in earlier | ||
versions. | versions. | ||
Line 140: | Line 140: | ||
<br /> | <br /> | ||
'''NOTE''': In the following sample criteria, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when recreating these criteria. | '''NOTE''': In the following sample criteria, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when recreating these criteria. | ||
* Like Forms!FormName!ControlName & | * Like Forms!FormName!ControlName & "*" Or _ Forms!FormName!ControlName Is Null<br /> | ||
<br /> | <br /> | ||
The criteria are the same as the earlier QBF sample, except that you can query by using a wildcard. For example, if you enter <span class="kbd userinput"> Jo</span> in a field by using this criterium, the query returns every record in the field that begins with "Jo," (Johnson, Jones, and Johanna) instead of returning only those records with an exact match to "Jo." | The criteria are the same as the earlier QBF sample, except that you can query by using a wildcard. For example, if you enter <span class="kbd userinput"> Jo</span> in a field by using this criterium, the query returns every record in the field that begins with "Jo," (Johnson, Jones, and Johanna) instead of returning only those records with an exact match to "Jo." | ||
Line 147: | Line 147: | ||
You can use criteria to query a date field by using the Start Date and the End Date text boxes that are on the query form. Records that have start dates and end dates that fall between the values you specify on the query form are returned. If you omit a Start Date value on the form, however, the query returns all records, regardless of the End Date value. | You can use criteria to query a date field by using the Start Date and the End Date text boxes that are on the query form. Records that have start dates and end dates that fall between the values you specify on the query form are returned. If you omit a Start Date value on the form, however, the query returns all records, regardless of the End Date value. | ||
* Like Forms!FormName!ControlName & | * Like Forms!FormName!ControlName & "*" Or Is Null<br /> | ||
<br /> | <br /> | ||
The criteria returns both records that match the criteria and records that are null. If the criteria are null, all the records are returned. Note that the asterisk (*) is considered a parameter because the asterisk is part of a larger Like expression. Because the asterisk is a hard-coded criteria value (for example, Like "*") records with null values are returned. | The criteria returns both records that match the criteria and records that are null. If the criteria are null, all the records are returned. Note that the asterisk (*) is considered a parameter because the asterisk is part of a larger Like expression. Because the asterisk is a hard-coded criteria value (for example, Like "*") records with null values are returned. |
Latest revision as of 14:19, 21 July 2020
Article ID: 95931
Article Last Modified on 1/18/2007
APPLIES TO
- Microsoft Access 97 Standard Edition
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
This article was previously published under Q95931
Moderate: Requires basic macro, coding, and interoperability skills.
For a Microsoft Access 2002 version of this article, see 304428.
For a Microsoft Access 2000 version of this article, see 209645.
SUMMARY
This article describes how to use a form to specify the criteria for a query in Microsoft Access. This technique is called query by form (QBF).
MORE INFORMATION
You can use the QBF technique to create a query form in which you can enter query criteria. The query form contains blank text boxes. Each blank text box represents a field in a table that you want to query. You make entries in only the text boxes for which you want to specify search criteria.
The query form is similar to a data entry form, such as the following sample query form:
First Name: __________ Last Name: __________ City: __________ State: __________ Zip Code: __________
You can enter any combination of search criteria in the form. You can specify a City only, or a City and a State, or a Zip Code only, or any other combination of values. Fields that you leave blank on the form are ignored. No search criteria are applied to those fields. When you click Search on the form, a query is run that uses the search criteria from your form.
To create a query form, follow these steps:
- Open the sample database Northwind.mdb (or Nwind.mdb in version 1.x or 2.0).
Create the following new form that is not based on any table or query. Save the form as QBF_Form:
Form: QBF_Form --------------------------------- Text box: Control Name: What Customer ID Text box: Control Name: What Employee ID Command button: Caption: Search OnClick (or OnPush in version 1.x): QBF_Macro
Create the following new macro, and then save the macro as QBF_Macro:
Macro: QBF_Macro ------------------------ OpenQuery Query Name: QBF_Query View: Datasheet Data Mode: Edit
Create the following new query that is based on the Orders table, and then save the query as QBF_Query:
NOTE: In the following sample criteria, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when you recreate these, and then make sure that the listed criteria for each field are in a single criteria line. If you put the statements on two separate lines, you see unexpected results.Query: QBF_Query --------------------------------------------------------- Field: CustomerID Sort: Ascending Criteria: Forms![QBF_Form]![What Customer ID] Or _ Forms![QBF_Form]![What Customer ID] Is Null Field: EmployeeID Sort: Ascending Criteria: Forms![QBF_Form]![What Employee ID] Or _ Forms![QBF_Form]![What Employee ID] Is Null Field: OrderID Field: OrderDate
View the QBF_Form in Form view. Enter the following combinations of criteria. Click Search after each combination:
Customer ID Employee ID Result ----------------------------------------------------------------- <blank> <blank> All 830 orders in Microsoft Access 97 and 7.0; 1078 orders in earlier versions. AROUT <blank> 13 orders for AROUT in Microsoft Access 97 and 7.0; 14 orders in earlier versions. AROUT 4 4 AROUT orders for employee 4. <blank> 4 156 orders for employee 4 in Microsoft Access 97 and 7.0; 209 in earlier versions.
After you view the result set for each query, close the Datasheet window before you begin your next search. Each time you click Search, the parameters in the QBF query filter the data according to the search criteria that are specified on the QBF query form.
Notes on the QBF Parameter Criteria
The earlier sample QBF query implements criteria in the query as:
Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
to filter the data. The criteria return all matching records. If the criteria are null, all the records are returned for the specified field.
You can specify any of the following alternative criteria to return slightly different results:
NOTE: In the following sample criteria, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when recreating these criteria.
- Like Forms!FormName!ControlName & "*" Or _ Forms!FormName!ControlName Is Null
The criteria are the same as the earlier QBF sample, except that you can query by using a wildcard. For example, if you enter Jo in a field by using this criterium, the query returns every record in the field that begins with "Jo," (Johnson, Jones, and Johanna) instead of returning only those records with an exact match to "Jo."
- Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _ Forms!FormName!StartDate Is Null
You can use criteria to query a date field by using the Start Date and the End Date text boxes that are on the query form. Records that have start dates and end dates that fall between the values you specify on the query form are returned. If you omit a Start Date value on the form, however, the query returns all records, regardless of the End Date value.
- Like Forms!FormName!ControlName & "*" Or Is Null
The criteria returns both records that match the criteria and records that are null. If the criteria are null, all the records are returned. Note that the asterisk (*) is considered a parameter because the asterisk is part of a larger Like expression. Because the asterisk is a hard-coded criteria value (for example, Like "*") records with null values are returned.
- Like IIf(IsNull(Forms!FormName![ControlName]), _ "*",[Forms]![FormName]![ControlName])
The criteria returns all the records that match the criteria. If no criteria are specified in the query form, all records that are not null are returned.
- IIf(IsNull(Forms!FormName![ControlName]), _ [FieldName],[Forms]![FormName]![ControlName])
This returns all the records that match the criteria. If no criteria are specified in the query form, all records that are not null are returned. This is the same result as in the earlier example.
REFERENCES
For additional information about parameter queries, click the article numbers below to view the articles in the Microsoft Knowledge Base:
100131 ACC: How to Create a Parameter In() Statement
103181 ACC: Showing All Records (Including Null) in a Parameter Query
Additional query words: qbe query by example inf
Keywords: kbhowto KB95931