This article shows you how to modify query properties in
Microsoft Access. This information is useful if you want to perform such tasks
as:
- Change the display of fields.
- Create top value queries.
- Add an alias for a table.
- Change field captions and formatting.
Query Properties
To view all the query properties, in Design view of any query,
right-click in an empty space in the query design window, and then click
Properties on the shortcut menu.
The following table lists all the
query properties and explains what each one is used for.
Collapse this tableExpand this table
| Property Name | Description |
|---|
| Description | You can use the Description property to provide information
about objects contained in the Database window as well as about individual
table or query fields. |
| Output All Fields | You can use the OutputAllFields property to show all fields in
the query's underlying data source and in the field list of a form or report.
Setting this property is an easy way to show all fields without having to click
the Show box in the query design grid for each field in the query. |
| Top Values | You can use the TopValues property to return a specified number
of records or a percentage of records that meet the criteria you specify. For
example, you might want to return the top 10 values or the top 25 percent of
all values in a field. |
| Unique Values | You can use the UniqueValues property when you want to omit
records that contain duplicate data in the fields displayed in Datasheet view.
For example, if a query's output includes more than one field, the combination
of values from all fields must be unique for a given record to be included in
the results. |
| Unique Records | You can use the UniqueRecords property to specify whether to
return only unique records based on all fields in the underlying data source,
not just those fields present in the query itself. |
| Run Permissions | You can use the RunPermissions property in a multiuser
environment with a secure workgroup to override the existing user permissions.
This allows you to view a query or run an append, delete, make-table, or update
query that you otherwise would not have permission to run. For example, as a
user, you may have read-only permission for queries, while the owner of the
queries has read/write permission. If the owner sets the RunPermissions
property to specify the owner's permissions, you can run an append query to add
records to a table. |
| Source Database | The SourceDatabase property specifies the external database in
which the source tables or queries for a query reside. |
| Source Connect Str | The SourceConnectStr property specifies the name of the
application used to create an external database. |
| Record Locks | You can use the RecordLocks property to determine how records
are locked and what happens when two users try to edit the same record at the
same time. |
| Recordset Type | You can use the RecordsetType property to specify what kind of
recordset is available. |
| ODBC Timeout | You can use the ODBCTimeout property to specify the number of
seconds Microsoft Access waits before a time-out error occurs when a query is
run on an Open Database Connectivity (ODBC) database. By setting this property
to zero (0), no time out will occur. |
| Filter | You can use the Filter property to specify a subset of records
to be displayed when a filter is applied to a form, a report, a query, or a
table. |
| Order By | You can use the OrderBy property to specify how you want to
sort records in a form, a query, a report, or a table. |
| Max Records | Specifies the maximum number of records that will be returned
by an ODBC database to a Microsoft Access database (.mdb). |
Field Properties
To see the field properties, in Design view of any query,
right-click in the field in the query design grid, and then click
Properties on the shortcut menu.
The following table lists all the
field properties and explains what each one is used for.
Collapse this tableExpand this table
| Property Name | Description |
|---|
| Description | You can use the Description property to provide information
about objects contained in the Database window as well as about individual
table or query fields. |
| Format | You can use the Format property to customize the way numbers,
dates, times, and text are displayed and printed. For example, if you have
created a Price text box, you can set its Format property to Currency and its
DecimalPlaces property to 2 or to Auto. If you enter 4321.678 in the control,
the number would be displayed as $4,321.68. You can use one of the predefined
formats or you can create a custom format by using formatting symbols. |
| Input Mask | You can use the InputMask property to make data entry easier
and to control the values that users can enter in a text box control. For
example, you could create an input mask for a Phone Number field that shows you
exactly how to enter a new number: (___) ___-____. It is often easier to use
the Input Mask Wizard to set the property for you. |
| Caption | You can use the Caption property to provide helpful information
to the user through captions on objects in various views: Field captions
specify the text for labels attached to controls created by dragging a field
from the field list and serves as the column heading for the field in table or
query Datasheet view. Form captions specify the text that appears in the title
bar in Form view. Report captions specify the title of the report in print
preview. Button and label captions specify the text that appears in the
control. |
| Display Control | You can use the DisplayControl property in table Design view to
specify the default control that you want to use for displaying a field. |
Field List Properties
To see the field list properties, in Design view of any query,
right-click on a field in the field list, and then click
Properties on the shortcut menu.
The following table lists all the
field list properties and explains what each one is used for.
Collapse this tableExpand this table
| Property Name | Description |
|---|
| Alias | You can use the Alias property to specify a custom name for a
source table or query when you use the same table or query more than once in
the same query. |
| Source | You can use the Source property to specify the source
connection string and source database for a query's source table or query. |
For additional information about general field
properties, click the article number below to view the article in the Microsoft
Knowledge Base:
304445
(http://support.microsoft.com/kb/304445/EN-US/
)
ACC97: Explanation of Data Types and Field Properties in a Microsoft Access Database
For additional information about lookup
fields, click the article number below to view the article in the Microsoft
Knowledge Base:
304464
(http://support.microsoft.com/kb/304464/EN-US/
)
ACC97: How to Add Lookup Fields in a Microsoft Access Table
Back to the top
