Introduction to data types and field properties

Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

All tables in Access are composed of fields. A field’s properties outline the characteristics and behaviour of data inserted to that field. A field’s data type is the prominent property because it influences what type of data the field can retain. This article summarises the data variants and other field properties available in Access, and contains supplementary information in a detailed data type reference section.

In this article

Overview

Data types can appear confusing for example, if a field’s data type is Text, it can occupy hybrid data with either text or numerical characters. But a field whose data type is Number can keep solely numerical data. So, you must know what properties are used with every data type.

A field’s data type affects other multiple vital field qualities, like the following:

  • Which formats can be used with the field.
  • The maximum size of a field value.
  • How the field can be used in expressions.
  • Whether the field can be indexed.

A field’s data type can be automatically generated redefined or you will choose a data type according to how you design the new field. For example, if you produce a field from the Datasheet view and:

  • Use a current field from another table, the data type is predefined in the template or in the other table.
  • Type data in a blank column (or field), Access relays a data type to the field determined by the values that you state or you can transfer the data type and format for the field.
  • On the Modify Fields tab, in the Fields & Columns group, pick Add Fields, Access presents a list of data types that you can choose from.

Top of Page

When to use which data type

Envision a field’s data type as a set of qualities that relates to each of the values that are included in the field. For example, values that are retained in a Text field can have only letters, numbers, and a capped group of punctuation characters, and a Text field can merely include a maximum of 255 characters.

Tip: Sometimes, the data in a field may look like one data type, but is actually another. For example, a field may seem to have numeric values but may actually include text values, such as room numbers. You can regularly use an expression to compare or transform values of different data types.

The following tables present you the formats available for each data type and explain the impact of the formatting option.

Basic Types

FormatUse to display
TextShort, alphanumeric values, including a last name or a street address. Note, beginning in Access 2013, Text data types have been renamed to Short Text.
Number, Large NumberNumeric values, such as distances. Note that there is a separate data type for currency.
CurrencyMonetary values.
Yes/NoYes and No values and fields that have only one of two values.
Date/TimeDate and Time values for the years 100 until 9999.
Rich TextText or combinations of text and numbers that can be formatted using colour and font controls.
Calculated FieldResults of a calculation. The calculation must link to other fields in the same table. You would use the Expression Builder to build the calculation. Note, Calculated fields were first introduced in Access 2010.
AttachmentAttached images, spreadsheet files, documents, charts, and other types of supported files to the records in your database, akin to attaching files to e-mail messages.
HyperlinkText or combinations of text and numbers preserved as text and used as a hyperlink address.
MemoLong blocks of text. A common use of a Memo field would be a detailed product description. Note, beginning in Access 2013, Memo data types have been renamed to Long Text.
LookupShows either a list of values that is retrieved from a table or query, or a set of values that you elaborated once you made the field. The Lookup Wizard commences and you can design a Lookup field. The data type of a Lookup field is either Text or Number, based upon your choices in the wizard.

Lookup fields are equipped with an extra range of field properties, which are found on the Lookup tab in the Field Properties pane.

Note: Attachment and Calculated data types aren’t available in .mdb file formats.

Number

FormatUse to display
GeneralNumbers without additional formatting exactly as it is stored.
CurrencyGeneral monetary values.
EuroGeneral monetary values stored in the EU format.
FixedNumeric data.
StandardNumeric data with decimal.
PercentagePercentages.
ScientificCalculations.

Date and Time

FormatUse to display
Short DateDisplay the date in a short format. Depends on your regional date and time settings. For example, 3/14/2001 for USA.
Medium DateDisplay the date in medium format. For example, 3-Apr-09 for USA.
Long DateDisplay the date in a long format. Depends on you’re the regional date and time settings. For example, Wednesday, March 14, 2001 for USA.
Time am/pmDisplay the time only using a 12 hour format that will respond to changes in the regional date and time settings.
Medium TimeDisplay the time followed by AM/PM.
Time 24hourDisplay the time only using a 24 hour format that will respond to changes in the regional date and time settings

Yes/No

Data TypeUse to display
Check BoxA check box.
Yes/NoYes or No options
True/FalseTrue or False options.
On/OffOn or Off options.

OLE Object  – OLE objects, such as Word documents.

Top of Page

Field size property

Following your creation of a field and confirming its data type, you can set further field properties. The field’s data type influences which other properties that you can set. For example, you can control the size of a Text field by setting its Field Size property.

For Number and Currency fields, the Field Size property is particularly vital, because it affects the range of field values. For example, a one-bit Number field can store only integers ranging from 0 to 255.

The Field Size property also dictates how much disc space each Number field value needs. According to the field size, the number can use exactly 1, 2, 4, 8, 12, or 16 bytes.

Note: Text and Memo fields have variable field value sizes. For these data types, Field Size sets the maximum space available for any one value.

For more details about field properties and how they function with the different data types, refer to the Data type reference section. Also, read the article Set the field size.

Top of Page

Data types in relationships and joins

A table relationship is an association between frequent fields in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.

A join is a SQL operation that integrates data from two sources into one record in a query record-set determined by values in a defined field that the sources share a consistency in. A join can be an inner join, a left outer join, or a right outer join.

Once you design a table relationship or insert a join to a query, the fields that you connect must own the same or matching data types. For example, you cannot create a join between a Number field and a Text field, even if the values in those fields match.

In a relationship or a join, fields that are set to the AutoNumber data type are compatible with fields that are set to the Number data type if the Field Size property of the latter is Long Integer.

You are unable to alter the data type or the Field Size property of a field that is involved in a table relationship. You can temporarily erase the relationship to adapt the Field Size property. However, if you modify the data type, you won’t be able to re-create the relationship without first also altering the data type of the related field. For more information on tables, see the article, Introduction to tables.

Top of Page

Data type reference

After you set a data type to a field, it includes a set of properties that you can pick. Select on data types below for further information.

Attachment

Purpose –    Used in a field that permits attaching files or images to a record. For example, if you have a job contacts database, you can use an attachment field to attach a photo of the contact, or attach documents like a C.V. For some file types, Access compresses every attachment whenever you add it. Attachment data types are available only in .accdb file format databases.

Types of attachments that Access compresses

Once you attach any of the following file types, Access compresses the file.

  • Bitmaps, such as .bmp files
  • Windows Metafiles, including .emf files
  • Exchangeable File Format files (.exif files)
  • Icons
  • Tagged Image File Format files

You can attach several varieties of files to a record. However, some file types that could pose security risks are blocked. As a rule, you can attach any file that was made in one of the Microsoft Office programmes. You can even attach log files (.log), text files (.text, .txt), and compressed .zip files. For a list of supported image file formats, check the table later in this section.

List of blocked file types

Access blocks the following kinds of attached files.

.ade.ins.mda.scr
.adp.isp.mdb.sct
.app.its.mde.shb
.asp.js.mdt.shs
.bas.jse.mdw.tmp
.bat.ksh.mdz.url
.cer.lnk.msc.vb
.chm.mad.msi.vbe
.cmd.maf.msp.vbs
.com.mag.mst.vsmacros
.cpl.mam.ops.vss
.crt.maq.pcd.vst
.csh.mar.pif.vsw
.exe.mas.prf.ws
.fxp.mat.prg.wsc
.hlp.mau.pst.wsf
.hta.mav.reg.wsh
.inf.maw.scf

Supported field properties

PropertyUse
CaptionThe label text that is shown for this field by default in forms, reports, and queries. If this property is blank, the field name is used. Any text string is accepted.

An effective caption is usually concise.
RequiredRequires that every record comes with at least one attachment for the field.

Supported image file formats

Access supports the following graphic file formats without having to configure extra software on your computer.

  • Windows Bitmap (.bmp files)
  • Run Length Encoded Bitmap (.rle files)
  • Device Independent Bitmap (.dib files)
  • Graphics Interchange Format (.gif files)
  • Joint Photographic Experts Group (.jpe, .jpeg, and .jpg files)

  • Exchangeable File Format (.exif files)

  • Portable Network Graphics (.png files)

  • Tagged Image File Format (.tif and .tiff files)

  • Icon (.ico and .icon files)

  • Windows Metafile (.wmf files)

  • Enhanced Metafile (.emf files)

File naming conventions

The names of your attached files can embody any Unicode character supported by the NTFS file system that is applied in Microsoft Windows NT. Also, file names are obliged to follow the following guidelines:

  • Names must avoid exceeding 255 characters, including the file name extensions.
  • Names are forbidden from including the following characters: question marks (?), quotation marks (), forward or backward slashes (/ \), opening or closing brackets (< >), asterisks (*), vertical bars or pipes (|), colons (ūüôā, or paragraph marks (¬∂).

Top of Page

AutoNumber

Purpose¬†–¬†¬†¬†Use an AutoNumber field to offer a unique value that serves no other purpose than to make every record distinct. The most frequent use for an AutoNumber field is as a primary key, specifically when no appropriate natural key (a key that is based on a data field) is available.

An AutoNumber field value needs 4 or 16 bytes, based on the value of its Field Size property.

Suppose that you have a table that stores contacts’ information. You can use contact names as the primary key for that table, but how do you handle two contacts with exactly the same name? Names are incompatible natural keys, because they are typically common. If you use an AutoNumber field, all records will definitively own a unique identifier.

Note: You should not use an AutoNumber field to keep a count of the records in a table. AutoNumber values are not reused, so deleted records can result in gaps in your count. Moreover, an accurate count of records can be easily obtained by using a Totals row in a datasheet.

Supported field properties

PropertyUse
Field SizeDetermines the amount of space that is allocated for each value. For AutoNumber fields, only two values are allowed:

The Long Integer field size is used for AutoNumber fields that are not used as replication IDs. This is the default value. You should not change this value unless you are creating a replication ID field.

Note: Replication is not supported in databases that use a new file format, such as .accdb.

This setting makes AutoNumber fields compatible with other Long Integer Number fields when they are used in relationships or joins. Each field value requires 4 bytes of storage.

The Replication ID field size is used for AutoNumber fields that are used as replication IDs in a database replica. Do not use this value unless you are working in or implementing the design of a replicated database.

Each field value requires 16 bytes of storage.
New ValuesDetermines whether AutoNumber field increments with each new value or uses random numbers. Select one of the following:

Increment РStarts with the value 1 and incrementally increases by 1 for each new record.

Random РStarts with a random value and assigns a random value to each new record. Values are of the Long Integer field size, and range from -2,147,483,648 to 2,147,483,647.
FormatIf you are using an AutoNumber field as a primary key or as a Replication ID, you should not set this property. Otherwise, choose a number format that meets your specific needs.
CaptionThe label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

An effective caption is usually brief.
IndexedSpecifies whether the field has an index. There are three available values:

Yes (No duplicates)  РCreates a unique index on the field.

Yes (Duplicates OK) РCreates a non-unique index on the field.

No¬†–¬† Removes any index on the field.

Note: Do not change this property for a field that is used in a primary key. Without a unique index, it is possible to enter duplicate values, which can break any relationships in which the key is a part.

Although you can create an index on a single field by setting the Indexed field property, some kinds of indexes cannot be created in this manner. For example, you cannot create a multi-field index by setting this property.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignSpecifies the default alignment of text within a control.

Top of Page

Calculated

Purpose¬†¬†–¬†Use to store the results of a calculation.

The calculation must relate to other fields in the same table. You would use the Expression Builder to construct the calculation. Note, Calculated data types were first introduced in Access 2010. Calculated data types are available only in .accdb file format databases.

Supported field properties

PropertyUse
ExpressionThe result of this calculation will be kept in the calculated column. If this column has been saved, then only saved columns can be used in this expression.
Result TypeThe result of the calculation will be presented as this data type.
FormatInfluences the way that the field appears when it is illustrated or printed in datasheets or in forms or reports that are linked to the field. You can use any valid number format. In most cases, you should set the Format value to reflect the result type.
Decimal PlacesClarifies the number of decimal places to use when showing numbers.
CaptionThe label text that is revealed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is accepted.

An effective caption is usually brief.
Text AlignDefines the default alignment of text within a control.

Top of Page

Currency

Purpose¬†¬†–¬†¬†Use to store monetary data.

Data in a Currency field is not rounded off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. Each Currency field value requires 8 bytes of storage.

Supported field properties

PropertyUse
FormatAffects the way that the field appears when it is presented or printed in datasheets or in forms or reports that are connected to the field. You can use any valid number format. In most cases, you should set the Format value to Currency.
Decimal PlacesElaborates the number of decimal places to use when displaying numbers.
Input MaskPresents editing characters to guide data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.
CaptionThe label text that is shown for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is permitted.

An effective caption is typically brief.
Default ValueAutomatically relays the specified value to this field when a new record is added.
Validation RuleProvides an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.
Validation TextState a message to display when a value that is entered violates the expression in the Validation Rule property.
RequiredDemands that data be typed in the field.
IndexedSpecifies whether the field has an index. There are three available values:

Yes (No duplicates)¬†–¬† Generates a unique index on the field.

Yes (Duplicates OK)¬†–¬† Forms a non-unique index on the field.

No – Deletes any index on the field.

Note: Do not edit this property for a field that is used in a primary key.

Although you can make an index on a single field by setting the Indexed field property, some types of indexes cannot be designed in such a way.

For example, you cannot design a multi-field index by setting this property.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignSpecifies the standard alignment of text within a control.

Top of Page

Date/Time

Purpose¬†¬†–¬†¬†Use to retain time-based data.

Supported field properties

PropertyUse
CaptionThe label text that is presented for this field by default in forms, reports, and queries. If this property is blank, the name of the field is applied. Any text string is accepted.

An effective caption is usually brief.
Default ValueIntuitively transfers the specified value to this field once a new record is added.
FormatDetermines the way that the field appears when it is displayed or printed in datasheets, or in forms or reports that are bound to the field. You can use a predefined format or build your own custom format.
List of predefined formats

General Date РAutomatically, if the value is a date only, no time is shown; if the value is a time only, no date is presented. This setting is a combination of the Short Date and Long Time settings.
Examples    

4/3/07

05:34:00 PM

4/3/07 05:34:00 PM

Long Date¬†–¬† Same as the Long Date setting in the regional settings of Windows. Example: Saturday, April 3, 2007.

Medium Date¬†–¬† Illustrates the date as dd-mmm-yyyy. Example: 3-Apr-2007.

Short Date –¬†Same as the Short Date setting in the regional settings of Windows. Example: 4/3/07.

Warning: The Short Date setting believes that dates between 1/1/00 and 12/31/29 are twenty-first century dates (that is, the years are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth century dates (that is, the years are assumed to be 1930 to 1999).

Long Time РSame as the setting on the Time tab in the regional settings of Windows. Example: 5:34:23 PM.

Medium Time РPresents the time as hours and minutes divided by the time separator character, followed by an AM/PM indicator. Example: 5:34 PM.

Short Time РConveys the time as hours and minutes split by the time separator, by using a 24-hour clock. Example: 17:34.

Lists of components that you can use in custom formats

Enter any combination of the following components to develop a custom format. For example, to display the week of the year and day of the week, type ww/w.

Important: Custom formats that are irregular with the date/time settings specified in Windows regional settings are skipped. For more information on Windows regional settings, see Windows Help.

Separator components

Note: Separators are set in the regional settings of Windows.

:   Time separator. For example, hh:mm

/   Date separator. For example, mmm/yyyy

Any short string of characters, contained within quotation marks (“”)¬†Custom separator. Quotation marks are not displayed. For example,¬†“,”¬†presents a comma.

Date format components
d¬†–¬† Day of the month in one or two numeric digits, as needed (1 to 31).

dd  РDay of the month in two numeric digits (01 to 31).

ddd  РFirst three letters of the weekday (Sun to Sat).

dddd  РFull name of the weekday (Sunday to Saturday).

w  РDay of the week (1 to 7).

ww  РWeek of the year (1 to 53).

m¬†–¬† Month of the year in one or two numeric digits, as needed (1 to 12).

mm¬†–¬† Month of the year in two numeric digits (01 to 12).

mmm¬†–¬† First three letters of the month (Jan to Dec).

mmmm¬†–¬† Full name of the month (January to December).

q  РThe quarter of the year (1 to 4).y   Number of the day of the year (1 to 366).

yy  РLast two digits of the year (01 to 99).

yyyy  РFull year (0100 to 9999).

Time format components

h  РHour in one or two digits, as needed (0 to 23).

hh  РHour in two digits (00 to 23).

n  РMinute in one or two digits, as needed (0 to 59).

nn  РMinute in two digits (00 to 59).

s  РSecond in one or two digits, as needed (0 to 59).

ss  РSecond in two digits (00 to 59).

Clock format components

AM/PM¬†¬†– Twelve-hour clock with the uppercase letters “AM” or “PM,” as appropriate. For example,¬†9:34PM.

am/pm¬†– Twelve-hour clock with the lowercase letters “am” or “pm,” as appropriate. For example,¬†9:34pm.

A/P¬†¬†– Twelve-hour clock with the uppercase letter “A” or “P,” as appropriate. For example,¬†9:34P.

a/p¬†¬†– Twelve-hour clock with the lowercase letter “a” or “p,” as appropriate. For example,¬†9:34p.

AMPM  РTwelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows.

Predefined formats

c – Same as the General Date predefined format.

ddddd  РSame as the Short Date predefined format.

dddddd РSame as the Long Date predefined format.

ttttt РSame as the Long Time predefined format.
IME ModeHandles the conversion of characters in East Asian versions of Windows.
IME Sentence ModeManages the conversion of sentences in East Asian versions of Windows.
IndexedClarifies whether the field has an index. There are three available values:

Yes (No duplicates)  РMakes a unique index on the field.

Yes (Duplicates OK)   Produces a non-unique index on the field.

No РErases any index on the field.

Note: Do not adjust this property for a field that is used in a primary key.

Even though you can generate an index on a single field by setting the Indexed field property, some types of indexes cannot be formed in this approach. For example, you cannot design a multi-field index by setting this property.
Input MaskPresents editing characters to aid data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.
RequiredForces you to enter data in that field.
Show Date PickerDefines whether to display the Date Picker control.

Note: If you use an input mask for a Date/Time field, the Date Picker control is unavailable regardless of how you set this property.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignElaborates the default alignment of text within a control.
Validation RuleGives an expression that must be true anytime you add or change the value in this field. Use alongside with the Validation Text property.
Validation TextState a message to present once a value that is entered violates the expression in the Validation Rule property.

Top of Page

Hyperlink

Purpose¬†¬†–¬†¬†Use to preserve a hyperlink, like an e-mail address or a Web site URL.

A hyperlink can be a UNC path or a URL. It can hold a maximum of 2048 characters.

Supported field properties

PropertyUse
Allow Zero LengthEnables entry (by setting to Yes) of a zero-length string (“”) in a Hyperlink, Text, or Memo field.
Append OnlyDecides whether to track field value changes. There are two settings:

Yes РTracks changes. To see the field value history, right-click the field, and then choose Show column history.

No –¬†Avoids tracking changes.

Warning: Warning Setting this property to No removes any existing field value history.
CaptionThe label text that is presented for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is permitted.

An effective caption is normally brief.
Default ValueAutonomously sends the specified value to this field when a new record is added.
FormatAffects the way that the field appears once it is presented or printed in datasheets or in forms or reports that are linked to the field. You can define a custom format for a Hyperlink field.
IME ModeManages the conversion of characters in East Asian versions of Windows.
IME Sentence ModeMaintains the conversion of sentences in East Asian versions of Windows.
IndexedSpecifies whether the field has an index. There are three available values:

Yes (No duplicates)  РGenerates a unique index on the field.

Yes (Duplicates OK) РForms a non-unique index on the field.

No РDeletes any index on the field.

Note: Do not update this property for a field that is used in a primary key.

Although you can produce an index on a single field by setting the Indexed field property, some variants of indexes are unable to be formed in this way. For example, you cannot design a multi-field index by setting this property.
RequiredMandates that data be typed in the field.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignElaborates the preset alignment of text within a control.
Unicode CompressionCompresses text that is occupied in this field as soon as under 4,096 characters are stored.
Validation RuleProvides an expression that must be true anytime you create or amend the value in this field. Use in conjunction with the Validation Text property.
Validation TextInput a message to present once a value that is entered breaches the expression in the Validation Rule property.

Top of Page

Memo

Purpose¬†–¬†Use to retain a block of text that exceeds 255 characters long and is formatted text. Note, beginning in Access 2013, Memo data type has been renamed to¬†Long Text.

Supported field properties

PropertyUse
Allow Zero LengthGrants entry (by setting to Yes) of a zero-length string (“”) in a Hyperlink, Text, or Memo field.
Append OnlyInfluences whether to track field value changes. There are two settings:

Yes РTracks changes. To check the field value history, right-click the field, and then select Show column history.

No РAvoids track changes.

Warning: Setting this property to No eliminates any existing field value history.
CaptionThe label text that is showcased for this field by default in forms, reports, and queries. If this property is blank, the name of the field is used. Any text string is taken.

Tip: An effective caption is usually brief.
Default ValuePromptly assigns the specified value to this field when a new record is added.
FormatDecides the way that the field appears when it is illustrated or printed in datasheets or in forms or reports that are associated to the field. You can define a custom format for a Memo field.
IME ModeManages the conversion of characters in East Asian versions of Windows.
IME Sentence ModeHandles the conversion of sentences in East Asian versions of Windows.
IndexedDictates whether the field has an index. There are three available values:

Yes (No duplicates) РGenerates a unique index on the field.

Yes (Duplicates OK) РMakes a non-unique index on the field.

No РErases any index on the field.

Note: Do not alter this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes are blocked from being designed in such a way. For example, you cannot create a multi-field index by setting this property.
RequiredObliges that data be entered in the field.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignElaborates the default alignment of text within a control.
Unicode CompressionCompresses text that is held in this field when less than 4,096 characters are stored.
Validation RuleOffers an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.
Validation TextType a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page

Number

Purpose¬†–¬†¬†¬†Use to hold a numeric value other than a monetary value. If you may use the values in the field to conduct a calculation, use the Number data type.

Supported field properties

PropertyUse
CaptionThe label text that is shown for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

An effective caption is usually brief.
Decimal PlacesDefines the number of decimal places to use when displaying numbers.
Default ValueAutomatically relays the specified value to this field when a new record is added.
Field SizeChoose one of the following:

Byte¬†‚ÄĒ Use for integers that range from 0 to 255. Storage requirement is 1 byte.

Integer¬†‚ÄĒ Use for integers that range from -32,768 to 32,767. Storage requirement is 2 bytes.

Long Integer¬†‚ÄĒ Use for integers that range from -2,147,483,648 to 2,147,483,647. Storage requirement is 4 bytes.

Tip:¬†Use¬†Long Integer¬†when you create a foreign key to relate to another table’s AutoNumber primary key field.

Single¬†‚ÄĒ¬† Use for numeric floating point values that range from -3.4 x 1038 to 3.4 x 1038 and up to seven significant digits. Storage requirement is 4 bytes.

Double¬†‚ÄĒ¬† Use for numeric floating point values that range from -1.797 x 10308 to 1.797 x 10308 and up to fifteen significant digits. Storage requirement is 8 bytes.

Replication ID¬†‚ÄĒ¬† Use for storing a globally unique identifier required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format.

Decimal¬†‚ÄĒ¬† Use for numeric values that range from -9.999… x 1027 to 9.999… x 1027. Storage requirement is 12 bytes.

Tip: For best performance, always specify the smallest sufficient Field Size.
FormatInfluences the way that the field appears when it is displayed or printed in datasheets, or in forms or reports that are bound to the field. You can use any valid number format.
IndexedDictates whether the field has an index. There are three available values:

Yes (No duplicates) РGenerates a unique index on the field.

Yes (Duplicates OK) РMakes a non-unique index on the field.

No РErases any index on the field.

Note: Do not alter this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes are blocked from being designed in such a way. For example, you cannot create a multi-field index by setting this property.
Input MaskLists editing characters to assist data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.
RequiredEnforces data entry into the field.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignElaborates the default alignment of text within a control.
Validation RuleProvides an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.
Validation TextState a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page

Large Number

Purpose¬†¬†–¬†¬†Use to occupy a Large numeric value apart from a monetary value. If you might use the values in the field to run a calculation, use the Large Number data type.

Supported field properties

PropertyUse
CaptionThe label text that is shown for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is accepted.

An effective caption is usually brief.
Decimal PlacesElaboratesthe number of decimal places to use when displaying numbers.
Default ValueAutomatically relays the specified value to this field once a new record is added.
FormatAffects the way that the field appears once it is displayed or printed in datasheets, or in forms or reports that are linked to the field. You can use any valid number format.
IndexedDictates whether the field has an index. There are three available values:

Yes (No duplicates) РGenerates a unique index on the field.

Yes (Duplicates OK) РMakes a non-unique index on the field.

No РErases any index on the field.

Note: Do not alter this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes are blocked from being designed in such a way. For example, you cannot create a multi-field index by setting this property.
Input MaskDisplays editing characters to guide data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.
RequiredRequires that data be entered in the field.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignSpecifies the default alignment of text within a control.
Validation RuleSupplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.
Validation TextEnter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page

OLE Object

Purpose¬†¬†–¬†¬†Use to attach an OLE Object, such as a Microsoft Office Excel spreadsheet, to a record. If you want to use OLE features, you must utilise the OLE Object data type.

In most cases, you should use an Attachment field rather than an OLE Object field. OLE Object fields support fewer file types than Attachment fields support. In addition, OLE Object fields prevent you from attaching several files to a single record.

Supported field properties

PropertyUse
CaptionThe label text that is presented for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is enabled.

An effective caption is usually brief.
RequiredMandates that data be entered in the field.
Text AlignDictates the default alignment of text within a control.

Top of Page

Text

Purpose¬†¬†–¬†¬†Use to store a maximum of 255 characters of text. Note, beginning in Access 2013, the Text data type has been renamed to¬†Short Text.

Supported field properties

PropertyUse
Allow Zero LengthPermits entry (by setting to Yes) of a zero-length string (“”) in a Hyperlink, Text, or Memo field.
CaptionThe label text that is shown for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is permitted.

An effective caption is usually brief.
Default ValueAutomatically groups the specified value to this field when a new record is added.
Field SizeType a value from 1 to 255. Text fields can range from 1 to 255 characters. For larger text fields, use the Memo data type.

Tip: For best performance, always specify the smallest sufficient Field Size.

For example, if you are storing postal codes of a known length, you should specify that length as the Field Size.
FormatInfluences the way that the field displays when it is presented or printed in datasheets or in forms or reports that are bound to the field. You can define a custom format for a Text field.
IME ModeManages the conversion of characters in East Asian versions of Windows.
IME Sentence ModeHandles the conversion of sentences in East Asian versions of Windows.
IndexedDictates whether the field has an index. There are three available values:

Yes (No duplicates) РGenerates a unique index on the field.

Yes (Duplicates OK) РMakes a non-unique index on the field.

No РErases any index on the field.

Note: Do not alter this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes are blocked from being designed in such a way. For example, you cannot create a multi-field index by setting this property.
RequiredCoerces data to be entered in the field.
Smart TagsAttaches a smart tag to the field. Smart tags were deprecated in Access 2013.
Text AlignClarifies the default alignment of text within a control.
Unicode CompressionCompresses text that is stored in this field when less than 4,096 characters are stored.
Validation RuleGives an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.
Validation TextInput a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page

Yes/No

Purpose¬†¬†–¬†¬†Use to store a Boolean value.

Supported field properties

PropertyUse
CaptionThe label text that is shown for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is accepted.

An effective caption is usually brief.
Default ValueInstantly transfers the specified value to this field once a new record is added.
FormatDetermines the way that the field appears when it is displayed or printed in datasheets, or in forms or reports that are bound to the field. Choose one of the following:

True/False  РLists the value as either True or False.

Yes/No РPresents the value as either Yes or No.

On/Off  РShows the value as either On or Off.
IndexedDictates whether the field has an index. There are three available values:

Yes (No duplicates) РGenerates a unique index on the field.

Yes (Duplicates OK) РMakes a non-unique index on the field.

No РErases any index on the field.

Note: Do not alter this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes are blocked from being designed in such a way. For example, you cannot create a multi-field index by setting this property.
Text AlignDefines the default alignment of text within a control.
Validation RuleProvides an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.
Validation TextState a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page

Leave a Reply

%d bloggers like this: