Change a field name in a query (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

Changing a field name in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) changes the column heading in query Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.). In addition, if you create a new object, such as a new form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), based on the query, the new object uses the new field name. For example, if you create a control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) to display the renamed field, the new field name will be used for the control name and its label caption. The field name in the underlying table doesn't change, however, nor does it change in forms and reports that were based on the query before you changed the field name.

You can rename a field in a query to describe the field's data more accurately. This is particularly useful when you define a new calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) or calculate a sum, count, or another type of total on an existing field. In such cases, unless you enter a name, Microsoft Access assigns names such as Expr1 or SumOfOrder Amount.

ShowRename a field

 Note   Changing a field's name in the current query's design grid has no effect in query Datasheet view if the Caption property for that field has been set in the underlying table or query.

  1. Open a query in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Place the insertion point to the left of the first letter of the field name in the design grid.
  3. Type the new name followed by a colon.

Change a field heading that shows in a query

 Note   If you're replacing the field name Expr1 (or Expr2, and so on) that Access automatically assigned, replace only the name, not the expression that follows the colon.

If you want the new field name to be used only for datasheet columns, or label captions in forms, reports, and data access pages, you can set the Caption property for the field instead of renaming the field in the design grid.

ShowChange a field's caption

  1. Open a query in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. In the design grid, click anywhere in the column of the field whose caption you want to change, and then click Properties Button image on the toolbar.
  3. In the Caption property box, type the new caption for the field.
 
 
Applies to:
Access 2003