Using Access Query Field Properties

Earlier in this course you learned how to set properties on fields inside tables — properties such as Format, Input Mask, and Caption that control how data is stored and displayed. Those same properties are available on fields inside queries, and knowing when and why to use them at the query level rather than the table level is one of the more practical skills you will develop as you build more sophisticated Access applications.

The general rule is straightforward: fields that come from a table and are displayed without modification will already carry whatever properties the table designer set. If the table's Rate field is formatted as Currency and given the caption Hourly Rate, that formatting and that caption flow through automatically into any query that includes the field. You do not need to touch the query's field properties at all.

The situation changes as soon as you create a calculated field. A calculated field does not exist in any table — it is an expression that Access evaluates on the fly, such as Cost: [Rate]*[HoursWorked]. Because it has no table definition behind it, it starts with no format, no caption, no description, and no decimal-places setting. Left alone, Access will display the raw numeric result with whatever default formatting applies to that data type, and the column header in Datasheet view will show the full expression text. Setting field properties on a calculated field is how you turn that raw output into something a user can actually read.

To access field properties in a query, open the query in Design View. Right-click the query in the Navigation Pane and choose Design View, or select the query and click the Design button on the ribbon under Query Tools.

Once in Design View, display the Property Sheet using any of these three methods:

• Click the Property Sheet button on the Design tab in the Show/Hide group.
• Right-click in the lower query design grid and choose Properties.
• Press F4 to toggle the panel open or closed.

With the Property Sheet visible, click the column for the field whose properties you want to set. The panel updates immediately to show the properties for that specific field. If you click in an empty area of the upper design window — the area where the table field lists appear — the Property Sheet switches to query-level properties instead of field-level properties. Those are two distinct contexts, and it is easy to set properties in the wrong one if you are not paying attention to where you clicked.

The screenshot above shows a Select Query built from two related tables: Projects and Hours. The design grid at the bottom contains three columns — ProjectDescription from the Projects table, DateWorked from the Hours table, and the calculated field Cost: [Rate]*[HoursWorked] with no table source. The Field Properties panel on the right is showing the properties for the calculated field, with Format already set to Currency.

The Field Properties panel has two tabs: General and Lookup. The General tab is where you will spend most of your time. It exposes the following properties for query fields:

Description

Text entered here is displayed on the Access status bar at the bottom of the screen when the user moves into that field in a form or datasheet. It is a good place for a brief explanation of the field's purpose — particularly useful on calculated fields that might otherwise be opaque to end users. It has no effect on query results.

Format

Controls how the field's value is displayed. You can choose a built-in format from the dropdown — Currency, Euro, Fixed, Standard, Percent, Scientific for numbers; General Date, Long Date, Medium Date, Short Date, and several time formats for dates — or you can type a custom format string directly. For example, #,##0.00 displays a number with a thousands separator and exactly two decimal places. mmmm dd, yyyy displays a date as June 15, 2009. Format affects only the display; the underlying stored value is unchanged. On a calculated field that produces a dollar amount, setting Format to Currency is the most common first step.

Decimal Places

Works in conjunction with the Format property to control how many digits appear after the decimal point. Setting this on the Cost calculated field to 2, combined with a Currency format, ensures results like $1,234.56 rather than $1,234.5600 or $1,235. The Auto setting delegates the decision to Access based on the Format chosen.

Input Mask

Defines how a user enters data into the field on a form. This property is available only on noncalculated fields — it does not apply to expressions — and it is more commonly set at the table level than at the query level. You might use it in a query if you are building a data-entry form directly on a query rather than on a table.

Caption

Replaces the field name or expression text as the column header in Datasheet view and as the default label on forms and reports. For the calculated field Cost: [Rate]*[HoursWorked], setting Caption to Project Cost means the column header reads Project Cost instead of the formula. This single property has an outsized effect on usability because it is what users see.

The Lookup tab in the Field Properties panel allows you to attach a lookup-style control to a field in the query. This is the same concept as a Lookup field in a table — instead of displaying a raw foreign-key value, the field shows a human-readable value drawn from another table or from a fixed value list. Configuring a lookup at the query level is less common than doing so at the table level, but it can be useful when you want different lookup behavior in a specific query without altering the underlying table definition.

The Caption property is the one you will set most often at the query level, so it is worth walking through a concrete example. Suppose you have a query that includes the calculated field Cost: [Rate]*[HoursWorked]. In Datasheet view, Access displays the column header exactly as the expression is written — including the colon, the brackets, and the multiplication operator. That is not useful for anyone reading a report.

To fix it, click the Cost: [Rate]*[HoursWorked] column in the design grid to select it, open the Field Properties panel with F4, click in the Caption row, and type Project Cost. Switch to Datasheet view and the column now reads Project Cost. The expression still calculates the same way; only the label has changed. If you later base a report on this query, the report inherits the caption as the default label for that field.

The screenshot above shows the Field Properties dialog as it appears in the current Access 365 interface. The General tab is active and all four editable properties are visible: Description, Format (with a dropdown arrow for built-in choices), Input Mask (with a builder button for the Input Mask Wizard), and Caption. The clean, card-based styling is the modernized 365 version of the same panel shown in the Design View screenshot earlier.

It is worth drawing a clear distinction between the two contexts the Property Sheet serves. When you click a field column in the design grid and open the Property Sheet, you are working with field-level properties — the ones discussed in this lesson. When you click in an empty area of the upper design window, the Property Sheet switches to query-level properties. These are different in kind:

• Top Values — limits results to the top N rows or the top N percent, useful for "top 10 customers" queries.
• Unique Values — equivalent to adding DISTINCT in SQL; suppresses duplicate output rows.
• Unique Records — suppresses duplicates based on all fields in the underlying tables, not just the output columns.
• Output All Fields — forces all fields from all source tables into the output regardless of the Show checkboxes in the grid.
• Default View — sets whether the query opens in Datasheet, PivotTable, or PivotChart view by default.

These query-level properties are independent of any individual field. Mixing them up with field-level properties — which happens easily if you accidentally click in the wrong area of Design View — is a common source of confusion for Access learners.

When a query includes a field directly from a table, it inherits whatever properties the table designer set. Format, Caption, and Decimal Places all flow through automatically. This inheritance is generally desirable — it means consistent display across every query, form, and report that uses the field.

You can override an inherited property at the query level by simply setting a different value in the Field Properties panel. That override applies only to this query's output; the table definition is unchanged, and other queries that include the same field will still show the table's original formatting. Use query-level overrides when a particular report or form needs a different label or format without disturbing the rest of the application.

One scenario where this comes up regularly is dates. A table might store dates in Short Date format (6/15/2009), which is compact and appropriate for a data-entry form. A report aimed at customers might need the same field displayed as Long Date (June 15, 2009). Setting Format to Long Date at the query level — rather than the table level — delivers the right appearance for the report without breaking the data-entry form.

For developers comfortable with SQL, some field properties can be expressed directly in the query's SQL statement rather than through the Property Sheet.

The Caption property corresponds to an alias in SQL. The expression Cost: [Rate]*[HoursWorked] in Design View translates to [Rate]*[HoursWorked] AS [Project Cost] in SQL View, where the text after AS is the alias that appears as the column header. If you prefer typing SQL to clicking in a grid, this is the equivalent approach.

The Format property has no direct SQL equivalent in the query statement itself; it is metadata stored in the query's property sheet, not part of the SELECT expression. You can simulate formatting in SQL using the Format() function — for example, Format([Rate]*[HoursWorked],"Currency") AS [Project Cost] — but this converts the numeric result to a string, which can interfere with sorting and aggregation. The Property Sheet approach is cleaner for formatting.

Click the Exercise link below to practice setting the Caption property for a field in a query.
Using Query Field Properties - Exercise