What are field properties in a database?

A field’s data type is the most important property because it determines what kind of data the field can store in MS Access tables.

Data types and field properties of tables

Every MS Access table is made up of fields. The field properties describe the characteristics and behavior of data added to that field. A field’s data type is the most important property as it determines the type of data the field can store.

A field’s data type determines other field qualities like the following:

• What formats can be used with the field
• The maximum size of a field value
• How the field can be used in an expression
• Can the field can be indexed?

The table below shows the formats available for each data type

FormatDisplayShort TextShort, alphanumeric values, such as a first name or a house numberNumber, Large NumberNumeric values such as marks in a subjectCurrencyMonetary values like course feesYes/NoYes and No or true and falseDate/Time, Date/Time ExtendedDate/Time: Date and time values for the years 100 through 9999.
Date/Time Extended: Date and time values for the years 1 through 9999Calculated FieldResults of a calculation. The calculation always refers to other fields in the same table. We use the Expression Builder to create the calculation.AttachmentAttached photographs, spreadsheet files, word documents, charts, and other types of supported files to the records in our database. This is similar to e-mail messages attachments.HyperlinkText or combinations of text and numbers used as a hyperlink address for emails and websitesLong TextUsed to display Rich Text, which includes formatting like bold and underline and  it can store up to about a gigabyte of text.LookupDisplays either a list of values that is retrieved from a table or query, or a set of values that we specify when we create the field as in course names. When the Lookup Wizard starts we can create a Lookup field. The data type of a Lookup field is either Text or Number, depending on the choices that we make as we use the wizard.

The other details connected with the data types are explained in the video.

The ‘Append Only’ property in a Hyperlink determines whether to track field value changes. There are two settings:

Yes: Tracks changes. To view the field value history, right-click the field in datasheet view, and then click Show column history.

A property for a numeric field might be the number of decimal places displayed or the maximum number of characters a field can hold. See figure, which illustrates the comparison.

A comparison of a car's with a field's properties.

You need to be in Design view in order to view and change a table's field properties.

Just about every object in Access every heading on a report, every label on a form, every field in a table has its own set of properties that you can view and change. This property concept might seem a little confusing at first, but it's something you have to learn if you want to become proficient at using Microsoft Access. Because you can almost always change object properties, you can also sometimes think of an object's properties as its settings.

Over half of this tutorial is devoted to working with a table's field properties, so consider this your introduction to field properties and to properties in general.

1. Start Microsoft Access, if necessary, and open the Lesson 4 database.

   To view and modify the Field Properties for a table, you first need to open the table in Design view.

2. In the Database window, select the tblCustomers table and click the Design button.

   Other ways to switch to Design view are to open the table and click the View button on the toolbar, or open the table and select View » Design View from the menu.

   As you can see in figure, the table design window is broken into two sections. The top section contains the table's field names and the bottom section displays the properties for the selected field. Simply click the field name whose properties you want to view.

3. Click the LastName field.

   The lower Field Properties section of the window displays the properties for the LastName field. To change a field property, simply click the property box you want to change and enter the new settings simple.

Table describes all the field properties. Don't worry if some of them seem confusing you will get a lot of practice adjusting each and every one of these properties in this tutorial. It's important to note that certain types of fields have their own sets of properties. For example, number fields have a Decimal Places property while text fields do not.

Important Field Properties

Field Size

Text fields: The maximum number of characters (up to 255) that can be entered in the field. The default setting is 50.

Number / Currency fields: Stores the number as a Byte, Integer, Long Integer, Single, Double, or Replication ID. The default setting is Long Integer.

Format

How the data in the field will be displayed on the screen.

Input Mask

Creates a format or pattern in which data must be entered.

Decimal Places

The number of decimal places in Number and Currency fields.

Caption

A label for the field that will appear on forms. If you don't enter a caption, Access will use the field name as the caption.

Default Value

A value that Access enters automatically in the field for new records.

Validation Rule

An expression that limits the values that can be entered in the field.

Validation Text

The error message that appears when an incorrect or restricted value is entered in a field with a validation rule.

Required

Specify whether or not a value must be entered in the field. The default is No.

Allow Zero Length

Specify whether or not the field allows zero-length text strings (a string containing no characters). Zero-length text strings are useful if you must enter data in a field, but no data exists. For example, if a Social Security field requires data, but you don't know the social security number, you would enter a zero-length text string in the field. To enter a zero-length text string type "" in the cell. The cell will appear empty. The default is No.

Indexed

Specify whether or not you want to index the field to speed up searches and sorts performed on the field. The default is No.

To display a table in design view:

• open the table and click the view button on the toolbar.

or...

• in the database window, click the tables icon in the objects bar, select the table, and click the design button.

  What are examples of field properties?

  Field Properties.

  Field Size: sets the maximum number of characters that can be entered into a text field..

  Format: sets how the data is to be displayed in the table field..

  Input Mask: sets the rules as to how data can be entered so that data is entered correctly..

  What is fields in database with example?

  Fields are the components that provide structure for a table. You can't have a table without fields. For instance, you can create an empty table that has fields defined but no rows (records). In databases, fields are used to maintain relationships between tables.

  What are the different types of fields in a database?

  Types of data fields.

  List (text values).

  List (numeric values).

  Rich Text..

  Decimal..

  Integer..

  How can you set the properties of a field?

  Access opens the table in Datasheet view. Select the field (the column) that you want to change. On the Fields tab, in the Properties group, click the arrow in the drop-down list next to Data Type, and then select a data type. Save your changes.