Top of Page How data types affect updating Read this section if you are new to Access or unfamiliar with the concepts behind relational databases. Large updates become much easier to perform when you understand some of the basic principles of database design. When you design a database table, you select a data type for each of the fields in that table, a process that helps ensure more accurate data entry.
For example, suppose you specify the Number data type for a field because you need to calculate sales figures. If someone tries to enter text in that field, Access displays an error message and will not let that user save the changed record, a step that helps protect your figures. Show me how to view data types Access now provides two ways to view the data types for a table field. You can use the commands on the Datasheet tab, or you can open the table in Design view. The following sets of steps explain how to use both techniques.
View data types by using commands on the Datasheet tab In the Navigation Pane, double-click the table that you want to use. Click the field that you want to investigate. View data types in Design view In the Navigation Pane, right-click the table and click Design View on the shortcut menu. Access opens the table in the design grid, and the upper section of the grid shows the name and data type of each table field.
The data type set for each table field provides the first level of control over what you can and cannot enter into a field. In some cases, a data type setting prevents you from entering any information at all. The following table lists the data types that Office Access provides and describes how they affect data entry. Data type Effect on data entry Text Text fields accept either text or numeric characters, including delimited lists of items. In some cases, you can use conversion functions to perform calculations on the data in a Text field.
Memo You can enter large amounts of text and numeric data in this type of field. Also, if the database designer sets the field to support rich text formatting, you can apply the types of formatting that you normally find in word processing programs, such as Office Word For example, you can apply different fonts and font sizes to specific characters in your text, and make them bold or italic, and so on. For more information about using rich-text formatting in a Memo field, see the article Format data in rows and columns.
Like Text fields, you can also run conversion functions against the data in a Memo field. Number You can enter only numbers in this type of field, and you can perform calculations on the values in a Number field.
Depending on how the database designer sets the field, you may encounter these conditions: If the database designer set an input mask for the field a series of literal and placeholder characters that appear when you select the field , you must enter data in the spaces and the format that the mask provides. You cannot type a full month name such as July , or a two-digit year value.
If the designer did not create an input mask to control how you enter a date or time, you can enter the value using any valid date or time format.
For example, you can type 11 Oct. The database designer might apply a display format to the field. In that case, if not input mask is present, you can enter a value in almost any format, but Access displays the dates in accordance with the display format. For more information about input masks, see the article Format data in rows and columns.
Currency You can enter only currency values in this type of field. Also, you do not have to manually enter a currency symbol. AutoNumber You can not enter or change the data in this type of field at any time. Access increments the values in an AutoNumber field whenever you add a new record to a table. If you format the field to show a list, you can select either Yes or No, True or False, or On or Off from the list, again depending on the format applied to the field.
You cannot enter values in the list or change the values in the list directly from a form or table. OLE Object You use this type of field when you want to display data from a file created in another program. Attachments provide a faster, easier, and more flexible way to view data from other programs. See the Attachment entry, later in this table, for more information.
Hyperlink You can enter any data in this type of field, and Access wraps it in a Web address. Also, editing existing hyperlinks can be difficult because clicking a hyperlink field with your mouse starts your Web browser and takes you to the site specified in the link.
To edit a hyperlink field, you select an adjacent field, use the TAB or arrow keys to move the focus to the hyperlink field, and then press F2 to enable editing.
Attachment You can attach data from other programs to this type of field, but you cannot type or otherwise enter text or numeric data. For information about using an Attachment field, see the article Attach files and graphics to the records in your database.
Lookup Wizard The Lookup Wizard is not a data type. Instead, you use the wizard to create two types of drop-down lists: A value list uses a delimited list of items that you enter manually when you use the Lookup Wizard. Those values can be independent of any other data or object in your database. In contrast, a lookup field uses a query to retrieve data from one or more of the other tables in a database, or in another location, such as a server running Windows SharePoint Services 3.
The lookup field then displays the data in a drop-down list. By default, the Lookup Wizard sets the table field to the Number data type. You can work with lookup fields directly in tables, and also in forms and reports. Depending on how the database designer has set the Lookup field and the combo box, you can edit the items in the list and add items to the list.
To do that, the database designer must set a property for the Lookup field the property is called Limit To List, and the designer has to turn it off. If you cannot directly edit the values in a lookup list, you have to add or change the data in your predefined list of values, or in the table that serves as the source for the Lookup field. For information about doing so, see the section titled "Edit the items in a lookup field" in the article Add one or more records to a database.
Finally, when you create a Lookup field, you can optionally set it to support multiple values. When you do so, the resulting list displays a check box next to each list item, and you can select or clear as many of the items as needed.
This figure illustrates a typical multi-valued list: Top of Page How table field properties affect updating Read this section if you are new to Access or unfamiliar with the concepts behind relational databases.
You cannot perform large updates successfully unless you understand how the properties set for table fields affect updating. When you design a database, you typically start by designing one or more tables. As part of that process, you set properties for the fields in each table. For example, you can set a Text field to accept no more than 50 characters, and you can set a Number field to accept only currency values.
You can set most field properties by using Design view. However, you can also set some properties by using commands in the groups on the Ribbon, part of the Microsoft Office Fluent user interface.
For example, you can set visual formats for Text and Memo fields by using the commands in the Font group on the Home tab. For more information about using those commands, see the article Format data in rows and columns. Show me how to set or change the properties for a table field. View table properties by using commands on the Datasheet tab In the Navigation Pane, double-click the table that you want to use.
Access opens the table in the design grid. In the lower part of the grid, under Field Properties, click the General tab, if it isn't already selected. A lookup field is a table field that uses either a hard-coded list of values, or a query that retrieves values from one or more tables in a database. By default, the lookup field presents those values to you in the form of a list. Depending on how a database designer sets the lookup field, you can select one or more items from that list.
Lookup fields often confuse new Access users because a list of items appears in one location the list that Access creates from the data in the lookup field , but the data can reside in another location the table that contains the data. Remember that when you update the data in a lookup field, you must update the source table the second location. You cannot update a Lookup field from the list.
The following table lists the table properties that have the greatest effect on data entry and explains their impact. Property Location in table design grid Possible values Behavior when you try to enter data Field Size General tab The character limit applies only to fields set to the Text data type.
If you try to enter more than the specified number of characters, the field cuts them off. When turned off, the field will accept null values, meaning the field can remain blank. A null value is not the same thing as a zero. Zero is a digit, and Access can use it in calculations. A null is a missing, undefined, or unknown value. To create a zero-length string, you type a pair of double quotation marks with no space between them "" and press ENTER.