CIS-140, CHAPTR+4=Modifying Tables and Fields

For example, entering a rule such as .1 and ,100 in the Validation Rule property forces users to enter values between 1 and 100. Entering text such as

"Enter values between 1 and 100" in the Validation Text property tells users when they have made a mistake and how to fix the error.

Validation text specifies the text in the error message that appears when a user violates a validation rule. For example, the error message could say

"Please enter a date that is later than June 4, 1977." The maximum length for the Validation Text property is 255 characters.

Allow Zero Length

4. Click Yes

Set Data Validation Rules

5. Click OK.

Modify Table Properties

8. Click OK.

Set a Field Property in Datasheet View and Design View

9. Press Enter.

Rename a Table 3. OPEN the Comments table, right-click Comments in the Navigation Pane, and then select Rename from the shortcut menu.

A dialog box appears that states that you can't rename the database object 'Comments' while it's open. Close the dialog box.

Allowing Zero-Length Strings in a Field When the Allow Zero Length field property is set to Yes, you can enter zero-length strings in a field.

A zero-length string contains no characters; you use the string to indicate that you know no value exists for a particular field.

Delete a Table Take Note If the table was related to one or more additional tables,

Access would ask if you wanted to delete those relationships before deleting the table.

Deleting a Table Deleting an entire table is not a complex process; however, remember that when you delete an entire table you might break the functionality of your database.

Although you will be asked to confirm the deletion of a table, you can always undo the action. In this exercise, you delete a table.

New Values Set whether an AutoNumber field is incremented or assigned a random number. Input Mask Display editing characters to guide data entry.

Caption Set the text displayed by default as the column name in tables and labels for forms, reports, and queries.

Delete a Table 2. A confirmation message appears.

Click Yes to delete the table.

Rename a Table 4. CLOSE the

Comments table.

• Data types: When you design a database table, you define a data type for each field in the table, and that data type restricts what users can enter

For example, a Date/Time field accepts only dates and times, a Currency field accepts only monetary values, and so on.

• Table properties: Table properties provide very specific types of validation.

For example, you can use the Order By property to select one or more fields to specify the default sort order of rows in Datasheet view.

Field Property Use This Field Property To Field Size Set the maximum size for data stored as a Short Text, Number, or AutoNumber data type.

Format Customize the way the field appears when displayed or printed. Decimal Places Specify the number of decimal places to use when displaying numbers.

Access uses the field property settings when you view and edit data. For example, the Format, Input Mask, and Caption properties affect how your information appears in table and query datasheets.

In addition, any controls on new forms and reports that are based on the fields in the table inherit these same property settings by default.

Other table properties are more advanced and used less often.

In this exercise, you modify the description property for a table.

For example, you can format numbers to make them easier to read, or you can define a validation rule that must be satisfied for information to be entered in a field.

In this exercise, you set the Required field property in Datasheet view and both the Default Value and Field Size field properties in Design view.

Every field also has an associated group of settings called properties that define the appearance or behavior of the field.

In this section, you learn how to create fields and modify field properties.

Define Input Masks for Fields 5. Click Medium Date to select the DD-MON-YR date format and then click Next. The next screen in the

Input Mask Wizard appears. If you wanted to change the input mask, you would do so here. You can also test entering the values in the Try It text area.

Subdatasheet Name Specify whether a subdatasheet should appear in Datasheet view, and, if so, which table or query should supply the rows in the subdatasheet.

Link Child Fields List the fields in the table or query used for the subdatasheet that match this table's primary key field s).

Order By On Load Automatically apply the sort criteria in the Order By property by setting to Yes) when the table is opened in Datasheet view.

Order By On Provide an alternate method to the Order By On Load property by automatically apply

Text Format Choose Rich Text to store text as HTML and allow rich formatting. Choose Plain Text to store only text. Text Align Specify the default alignment of text within a field or control.

Precision Specify the total number of digits allowed, including those both to the right and the left of the decimal point when decimal Field Size property is set using Number data type).

Modify Table Properties 9. Click the Close button on the

Property Sheet pane to close it.

Table 4-2 lists the available field properties and what they control.

Remember that the properties available depend on the field's data type.

Modifying Table Properties You can set properties that control the appearance or behavior characteristics for an entire table in the table's property sheet.

Sometimes it is necessary to describe the purpose of a table by modifying the table's Description property since others who view your table may require more information about its purpose.

Table Property Use This Table Property To Subdatasheet Expanded Specify whether to expand all subdatasheets when you open the table.

Subdatasheet Height Specify whether to expand to show all available subdatasheet rows default) when opened or to set the height of the subdatasheet window to show when opened.

Set Data Validation Rules 7. Select the number 1900 and then replace it by typing 2016.

The property boxes should look like those shown

Rename a Table 7. Type Event Comments and then press Enter.

The table has been renamed.

• Field sizes: Field sizes provide another way to validate text. For example, if you create a field that stores first names, you can set it to accept a maximum of 15 characters.

This can prevent a malicious user from pasting large amounts of text into the field.

The Default Value property allows you to specify a value to automatically assign to a field when new records are added.

This is useful if you have data that consistently repeats. The Name & Caption property allows you to specify a new field name and the associated caption for that field.

Set a Field Property in Datasheet View and Design View 3. Click the Required check box in the Field Validation group on the Table Tools Fields contextual tab.

This setting determines that a value must be entered in the Location field for all records in the table.

Modify Table Properties 1. OPEN the Events database from the data files for this lesson and then SAVE the database as Events-final. Take Note Don't forget to click Enable Content here.

We stop indicating it as a separate step from this point on but always click Enable Content when prompted.

MODIFYING A DATABASE TABLE After a table has been created, you may need to modify it.

You can make many changes to a table—or other database object—using its property sheet.

Modify Table Properties 6. Press Shift+F2 to open the

Zoom dialog box to provide more space.

Orientation Set the view orientation, according to whether your language is read left-to-right or right-to-left. Description Provide

a description of the table. Default View Set Datasheet as the default view when you open the table.validation Rule Supply an expression that must be true for you to add a record or change a record.

• Field sizes: It could also prevent an inexperienced user from mistakenly entering a first, middle, and last name in

a field designed only to hold a first name.

Default Value Automatically assign a default value to a field when new records are added. Validation Rule Supply an expression that must be true whenever you

add or change the value in this field. Validation Text Enter text that appears when a value violates the Validation Rule. Required Require that data be entered in a field.

To delete a table or other database object like a report, form, or query, right-click it in the Navigation Pane

and click Remove or Delete. Or, select the table in the Navigation Pane and then press Delete.

In this exercise, you modify the Validation Rule and Validation Text properties for the End Date field. Data can be validated in several ways,

and you will often use multiple methods to define a validation rule. Each of the following can be used to ensure that your users enter data properly:

Allow Zero Length 3. Click the down

arrow to display the menu.

Define Input Masks for Fields 3. Click the Input Mask Wizard button. A message box appears

asking if you want to save the table now.

You can set a few of the available field properties in Datasheet view, but to access all of the available field properties, you must open the table in Design view. For example, you can modify the Field Size property in both Datasheet and Design views,

but you can only modify the Text Align property—which allows you to specify the default alignment of text within a field—in Design view.

You can add input masks to table fields by running the Input Mask Wizard or

by manually entering masks in the Input Mask field property.

In the Properties group, select the Field Size property box to define the text length for a field, which limits the number of

characters allowed for input. You can also select other field properties like Default Value, and Name & Caption.

Modify Table Properties 4. On the Design tab, in the Show/Hide group,

click Property Sheet. The Property Sheet pane appears on the right of the Access window

Rename a Table 1. On the Create tab, in the Templates group,

click the Application Parts button and then click Comments to create a new table.

Set a Field Property in Datasheet View and Design View 4. On the Home tab, in the Views group,

click the bottom half of the View button and then click Design View.

Define Input Masks for Fields 4. Click Yes to

close the message box and display the Input Mask Wizard

The maximum length for the Validation Rule property is 2,048 characters. For example, if the field

contains a date, you can require that the date entered in the field be later than June 4, 1977.

Set Data Validation Rules 3. Click the Expression Builder button to

display the Expression Builder dialog box

Define Input Masks for Fields 2. Click the Input Mask property box in the lower portion of the table design grid to

display the Input Mask Wizard button (...) on the far right of the cell.

Modify Table Properties 2. In the Navigation Pane,

double-click Events to open that table.

An input mask is helpful because it can prevent users from entering invalid data such as a phone number in a date field). In addition, input masks can ensure that users

enter data in a consistent way. In this exercise, you specify that the dates in the Start Date field be entered in Medium Date format, following the required pattern, 27-Sept-69.

Modify Table Properties 7. Type Most popular

events for 2016.

Defining Input Masks You use an input mask whenever you want users to enter data in a specific way. An input mask can require users to enter dates in a specific format,

for example, DD-MM-YYYY, or telephone numbers that follow the conventions for a specific country or region.

Validation Text Enter text that appears when a record violates the Validation Rule expression. Filter Define criteria to display only matching rows

in Datasheet view. Order By Select one or more fields to specify the default sort order of rows in Datasheet view.

Scale Specify the maximum number of digits that can be stored to the right of the decimal separator (when decimal Field Size property

is set using Number data type). Expression Specify the expression used for the Calculated data type to generate a value.

the records in the table. Or, select the Required check box to

make this a required field, where all instances of this field must contain a value.

The caption is what appears as column names in tables, and as labels in queries, forms, and reports. Keep in mind that Access will show field names as the column names and labels when

no caption property value is specified. The field name is what Access uses to reference the field behind the scenes and when you view the field names in Design view.

Setting Data Validation Rules Validation rules help ensure that your database users enter the proper types

or amounts of data. A validation rule is an expression that limits the values that can be entered in the field.

Set a Field Property in Datasheet View and Design View 6. In the Default Value row in the lower portion of the table design grid, click in the

property box and then type To Be Announced to specify a value that will be automatically entered in this field for new records.

Set Data Validation Rules 4. Select the number 1900 and

replace it by typing 2016.

• Field properties: You can also use field properties, such as the Validation Rule property to

require specific values, and the Validation Text property to alert your users to any mistakes.

Rename a Table 6. Click Rename. The table name i

s now selected for renaming

Modify Table Properties 10. Click the File tab and then click Save to

save the design changes you have made to the table.

Set a Field Property in Datasheet View and Design View 8. In the Field Size row in the lower portion of the table design grid,

select 150 in the property box and then type 175 to change the maximum number of characters you can enter in the Title field.

Rename a Table 2. In the Create Relationship dialog box that appears,

select There is no relationship and then click Create.

Setting Field Properties You can control the appearance of information, prevent incorrect entries, specify default values,

speed up searching and sorting, and control other appearance or behavior characteristics by setting or modifying field properties.

Result Type Specify the data type to be used to store the value generated by

the Expression field property for the Calculated data type.

Define Input Masks for Fields 7. Click Finish. The input mask appears in

the Input Mask row

Delete a Table 1. Right-click the Event Comments table in

the Navigation Pane and then click Delete.

To set a field property in Datasheet view, open the table in Datasheet view. Click in the field for which you want to set the property. In the Field Validation group on

the Table Tools Fields contextual tab, select the Unique check box to require the values in the field to be unique for all

Modify Table Properties 3. On the Home tab, in the Views group, click the bottom half of

the View button and then click Design View from the menu that appears.

You can also rename or delete a table, but keep in mind that such a change could possibly break the functionality of

the database, because in a relational database the various components work together.

CREATING FIELDS AND MODIFYING FIELD PROPERTIES A field has certain defining characteristics such as a name that uniquely identifies

the field within a table, and a data type that is chosen to match the information to be stored.

Link Master Fields List the primary key field(s) in this table that match the child fields for the subdatasheet. Filter On Load Automatically apply

the filter criteria in the Filter property by setting to Yes) when the table is opened in Datasheet view.

Set Data Validation Rules 6. Click the Validation Text property box in

the lower portion of the table design grid.

Set a Field Property in Datasheet View and Design View 5. In the Field Name column in

the middle portion of the table design grid, click in the Location cell.

Think carefully before you rename a table. If existing database objects, such as queries or reports, use data from that table,

the name modification might break the functionality of the database. In this exercise, you create a new table and then rename it using the shortcut menu.

Modify Table Properties 5. Place the insertion point in

the property box for Description.

Click the box for the field property you want to set. Alternatively, you can press F6 and then move to the property using the arrow keys. Type a setting for

the property or, if an arrow appears at the right side of the property box, click the arrow to choose from a list of settings for the property.

Set Data Validation Rules 2. Click the Validation Rule property box in the lower portion of

the table design grid to display the Expression Builder button ...) on the far right of the cell.

Allow Zero Length 2. Click the Allow Zero Length property box in the lower portion of

the table design grid to display the down arrow on the far right of the cell.

Allow Zero Length 1. In the Field Name column in the upper portion of

the table design grid, click in the Description cell.

Set a Field Property in Datasheet View and Design View 7. In the Field Name column in

the upper portion of the table design grid, click in the Title cell.

Renaming a Table To rename a table or other database object, you must first close it. In the Navigation Pane, locate and right-click the object that you want to rename and

then click Rename on the shortcut menu that appears. Or, select the table in the Navigation Pane, press F2, type a new name, and then press Enter.

Allow Zero Length Allow entry (by setting to Yes) of a zero-length string "") in a Short Text, Long Text, or Hyperlink field. Indexed Speed up access to data in

this field by creating and using an index. Unicode Compression Compress text stored in this field when a large amount of text is stored.

Define Input Masks for Fields 6. Click Next to accept the default settings in

this screen and display the final Input Mask Wizard screen.

Modify Table Properties 11. Click the Close button

to close the table.

Rename a Table 5. Right-click Comments in the Navigation Pane

to display the shortcut menu.

Set a Field Property in Datasheet View and Design View 1. Double-click the Events table in the Navigation Pane

to open the table in Datasheet view, if it is not already open.

Set a Field Property in Datasheet View and Design View 2. Click the Location column header

to select that field.

Define Input Masks for Fields 1. In the Field Name column in the

upper portion of the table design grid, click in the Start Date cell.

Set Data Validation Rules 1. In the Field Name column in the

upper portion of the table design grid, click the End Date cell.

IME Mode Specify an Input Method Editor, a tool for using English versions of Windows. IME Sentence Mode Specify the type of data you can enter by

using an Input Method Editor. Append Only Retain a history of all data changes (by setting to Yes) of a Long Text field.

This recognition of a nonexistent value actually represents a string. You enter a zero-length string by typing two double quotation marks

with no space between them ""). In this exercise, you modify the Allow Zero Length property for the Description field.

Field Properties Some field properties are available in Datasheet view, but to access the complete list of field properties

you must use Design view. An example of field properties for a table in Design view is shown

To set field properties in Design view, open the table in Design view. In the upper portion of the table design grid, click the field for which

you want to set properties. The properties for this field are displayed in the lower portion of the table design grid.