The Format property in Microsoft Access is used to customize the way text, date, time, numbers and other values are displayed.

Formatting the table field can be done in two ways.

  1. Can use the predefined formats available – There are different data types available and for each data type, different settings or different predefined format are used in Format property.
  2. Using the format symbols, one can create a custom format.

For whichever field, a format needs to be done, you can set a property in control’s property sheet. We can do this in two ways.

  1. Choose fields property section -> table design view.
  2. Choose field properties property sheet -> design view

Point to be noted : The format property only formats the data and it does not affect the data that is already stored.

Predefined formats

Following predefined formats are available for data types in Microsoft access .

  • Time/Date
  • Currency
  • Number
  • Memo and Text
  • Yes/No

These predefined and the display formats are region/country specific. This can be specified in Windows control panel by double clicking on the regional options.

Example :

If you select the country/region as United States, then the currency value of 534.25, then it appears as $534.25. Whereas, if you select, country/region as India, the same currency value appears as Rs.543.25

In visual basic, we write these predefined formats as,

Me!Date.Format = “Long Date”

Me!Time.Format = “Medium Time”

Me!Registered.Format = “Yes/No”

In table design view, if you set a format property for a field, Access uses the same format to display the data.

Custom formatting symbols

For any data type, the following symbols can be used in a custom format

  • If you choose the symbol as (space) then Access displays the spaces as literal characters
  • If you choose “abc”, then Access displays that is placed inside the quotation marks as literal characters.
  • If ‘!’ is chosen, then Access formats in left alignment instead of right alignment.
  • If ‘*’ is chosen, then Access fills the spaces with the next character.
  • If ‘\’ is chosen, then as a literal character, Access displays the next character
  • If ‘color’ is chosen, then the data will be displayed in the specified color.

Note : Custom formatting symbols for currency and number data types cannot be mixed with memo and text or Yes/No formatting symbols.

Example:

Forms!Employees!HireTime.Format = "DD/MM/YYYY"

The Precedence

For any particular data, if you have defined an input mask and the format property is also set, then the input mask is ignored and the format property takes the precedence. For an instance, if you create a Logn Id input mask in a table design view, and you had set the format property for that field, then the Login id input mask is ignored /omitted and the data is displayed as it is in the format property .