Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 36 ยท Tables and Relationships

Field Properties, Validation Rules and Lookups in Access

Control what goes into Access tables with Format, Input Mask, Default Value, Required, Validation Rule and Text, Allow Zero Length, Caption and Lookup combo boxes.

A field's data type decides what kind of value it holds. Its properties decide how that value looks, whether it may be left empty, what values are allowed and what appears when nothing is typed. Setting them well is the cheapest way to keep bad data out of your database: a validation rule that rejects a negative price costs one line, while cleaning up a thousand wrong prices later costs a weekend.

This lesson covers the properties you will use most in Design view, then shows validation rules, input masks and lookup lists in action.

Where properties live

Open a table in Design view, click a field and look at the Field Properties pane. The General tab lists properties that depend on the type; the Lookup tab sets up a combo or list box. Press F6 to move into the pane.

Common General properties (the exact list depends on the data type):

Property What it does
Field Size Maximum length of text or numeric size.
Format How the value is displayed, without changing what is stored.
Decimal Places Number of decimals displayed for Number and Currency.
Input Mask A pattern that guides typing, such as a phone number layout.
Caption A friendly column heading shown in datasheets, forms and reports.
Default Value The value a new record starts with.
Validation Rule An expression the value must satisfy.
Validation Text The message shown when the rule fails.
Required Whether the field may be left empty (Null).
Allow Zero Length Whether an empty string "" is permitted in text fields.
Indexed Whether Access builds an index for the field.

Three guards for good data

Required No blank allowed
Validation Rule Only valid values pass
Validation Text A clear error message

Set all three on fields that matter.

1 / 2

Format versus Input Mask

These two are often confused. Format changes only the display. The stored value stays the same. For example, a Date/Time field stored as 2026-03-05 can display as Thursday, March 5, 2026 with the Long Date format, or 05-Mar-26 with a custom format dd-mmm-yy.

Useful format codes for numbers and dates:

Code Meaning
0 A digit, always shown (000 shows 7 as 007)
# A digit, hidden if empty
, Thousands separator
dd/mm/yyyy Day, month, four-digit year
hh:nn Hours and minutes (nn is minutes, because mm means months)
> and < Force text to upper or lower case

An Input Mask controls what the user can type and can store literal characters. A phone mask like (999) 000-0000 shows blank positions that accept digits. In masks, 0 requires a digit, 9 accepts an optional digit, L requires a letter, A requires a letter or digit and & accepts any character. The Input Mask Wizard (click the ... button) offers ready-made masks for phone numbers, ZIP codes, dates and passwords. Choose the Password mask to show asterisks while typing.

Do not use a restrictive input mask on international data. A phone or postal code mask designed for one country will block customers from others.

Default Value

The Default Value appears in each new record. It can be a constant or an expression:

  • 0 for a numeric quantity.
  • "Pending" for a status text.
  • Date() for today's date.
  • Now() for the current date and time.
  • Yes for a Yes/No field.

Users can overwrite a default. Defaults are applied when the new record starts, not when it is saved later.

Required and Allow Zero Length

If Required is Yes, Access refuses to save a record where this field is Null. Primary keys are always required.

For text fields there is a second concept, Allow Zero Length. A Null means "no value was entered", while a zero-length string ("") means "the value is known to be empty". Having both can cause confusing queries, because WHERE Phone Is Null does not find rows where Phone is "". A common recommendation is to set Required = Yes and Allow Zero Length = No on text fields that must be filled in, and Required = No and Allow Zero Length = No on optional fields, so empty values are always Null.

The SQL equivalent is NOT NULL for required fields, which the next example uses together with a default.

Validation Rule and Validation Text

A Validation Rule is an expression that must be true for the value to be accepted. Write it without the field name: you are describing the value itself.

Field Validation Rule Validation Text
UnitPrice (Currency) >=0 Price cannot be negative.
Rating (Number) Between 1 And 5 Rating must be 1 to 5.
Email (Short Text) Like "*@*.*" Enter a valid email address.
Status (Short Text) In ("Open","Closed","On hold") Pick a listed status.
BirthDate (Date/Time) <=Date() Birth date cannot be in the future.

If you omit Validation Text, Access shows a generic message that quotes your rule, which is rarely friendly. Always write your own.

You can also set a table-level rule in the table's Property Sheet. It can compare fields: [EndDate]>=[StartDate]. Table rules run when a record is saved, not field by field.

In standard SQL, the same ideas are CHECK constraints. Try them:

SQL Validation with CHECK constraints

Press Run (or Ctrl + Enter) to see the result here.

Now break a rule on purpose. Run this and read the error, which is the SQL equivalent of Access showing your Validation Text:

SQL A rejected record

Press Run (or Ctrl + Enter) to see the result here.

In Access, the failing entry would pop up a dialog with your Validation Text and keep the cursor in the field until you fix the value.

Caption and Description

Caption replaces the field name as a column heading. A field named UnitPrice with the caption Price per unit shows the friendly heading in datasheets, forms and reports made later. Forms created after you set a Caption inherit it; older forms do not. Description in the design grid appears in the status bar as help text.

Indexed

Setting Indexed to Yes (Duplicates OK) or Yes (No Duplicates) speeds up searches and sorts on that field, and the second option enforces uniqueness. Indexes are the subject of the next lesson, primary keys and indexes.

Lookup fields: drop-down lists in tables

On the Lookup tab, set Display Control to Combo Box. Then set Row Source Type to:

  • Value List, with Row Source like "Open";"Closed";"On hold" (semicolon-separated), or
  • Table/Query, with a SELECT statement that fills the list from another table.

For a table-based lookup you usually set Bound Column to 1 (the key to store) and Column Count to 2 with Column Widths 0";1" so the key is hidden and the name is visible. Limit To List set to Yes prevents values outside the list.

SQL
SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName;

Users now see friendly names, but the table stores the number. This is convenient on forms, but lookups defined at the table level can confuse people who see a name in the datasheet while queries return a number. Many professionals set up lookups on form controls instead and keep tables plain. A proper relationship, as shown in the relationships lesson, gives you the same integrity without hiding anything.

The idea of a lookup is simply a join. This query is what a combo box with two columns would read:

SQL What a lookup combo box reads

Press Run (or Ctrl + Enter) to see the result here.

Multivalue fields

When you make a lookup field with Allow Multiple Values, Access stores several selections in one field. It looks handy but breaks the rules of normalized design and complicates queries, and it does not work well when you upsize to SQL Server. Use a separate junction table instead, as you will do in the relationships lesson.

A practical checklist

For every new field ask:

  1. What is the narrowest data type and field size that fits?
  2. Is it required?
  3. What is a sensible default?
  4. What values are definitely wrong, and can a validation rule catch them?
  5. Would a caption or description help the people using it?

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between Format and Input Mask?

    Format only affects display. An input mask guides data entry and can store literal characters.
  2. 2. Which Validation Rule limits a rating to values from 1 through 5?

    Between 1 And 5 is inclusive. Rules are written without the field name.
  3. 3. What does a Null in a text field mean compared with a zero-length string?

    They behave differently in queries: Is Null does not match an empty string.
  4. 4. Where do you set a rule that EndDate must not be earlier than StartDate?

    A table-level Validation Rule can compare several fields in the same record, such as [EndDate]>=[StartDate].

Summary

  • Field properties control display, defaults, required-ness and what values are accepted.
  • Format changes only the display; Input Mask guides typing.
  • Write a Validation Rule without the field name and add friendly Validation Text; use table-level rules to compare fields.
  • Required and Allow Zero Length decide how empty values are stored; keep them consistent.
  • Lookup combo boxes read from a value list or a table, but plain tables plus relationships are easier to maintain.

Next: Primary Keys and Indexes