How to Create a Table in Design View in Access
Design view provides a different view of the same table that you see in Datasheet view. Design view provides more options for controlling data entry. This is good for maintaining data integrity.
After creating a table in Datasheet view, switch to Design view by clicking the View icon in the Ribbon. If you're prompted to save the table, go ahead.
You can also switch to Design view by clicking the Design icon at the bottom-right corner.
The Datasheet icon next to it allows you to toggle between Datasheet view and Design view as needed.
How Design View Displays the Fields
In Design view, the fields are listed in rows (as opposed to columns like in Datasheet view). The first column lists the field names, the second column contains their respective data types.
Design view doesn't display any data (like in Datasheet view).
When you select a field in Design view, the bottom pane displays its properties. The field properties specify extra information about the type of data that can be entered into that field by a user.
Field properties are optional — you don't need to specify anything other than what's already there. However, field properties are a powerful way to maintain data integrity. By adjusting one or more field properties, you can ensure that your database doesn't get populated with eroneous data.
In the following example, the DateCreated field has been selected. We can see that the data type for this field is Date/Time.
But if we look down below in the Field Properties pane, we can see that we can make extra adjustments to this field if we need to. In this case, two extra properties have been specified (these are highlighted). The format of the data will be General Date and the default value will be =now() (this will automatically populate the field with the date and time that the record was first created).
The other properties that you can see in the Field Properties pane haven't been altered (those were the initial values that Access displayed).
Field Properties
The Field Properties pane (at the bottom of the screen) allows you to specify certain properties against each field. Here are some of the more commonly used ones.
Format
The Format property allows you to specify the format that the data is displayed. Note that it doesn't affect how the data is stored — only the way it's displayed to the user.
Microsoft Access provides predefined formats for Date/Time, Number and Currency, Text and Memo, and Yes/No data types.
Input Mask
The Input Mask property helps you control how the data is entered into the field. It allows you to ensure that the field contains only data that is acceptable to that field.
For example, you might want to make sure that whenever a user enters a date into a date field, that it is always in a certain format. If you don't do this, your data could start to look messy. One user might enter the date as "20 Sep 2016", another enters it as "20/09/16", another enters it as "09/20/2016", and another accidentally enters a phone number, etc.
In this case, you could use an input mask that requires the date to always be entered in only one format. If the date is not entered in the correct format, the record won't be saved.
Default Value
A Default Value is a value that is added to the field as soon as the record is created — without requiring the user to enter anything in that field.
Providing a default value can be useful in cases such as providing a timestamp (when the sytem automatically inserts the current time/date into the field) or providing initial user preferences (such as newsletter opt-in, etc).
Validation Rules
A Validation Rule restricts what users can enter into a field. It is a rule that you set up in order to prevent incorrect data being entered.
For example, a validation rule of <500 will ensure that only numbers less than 500 are entered. A validation rule of >500 And <1000 ensures that the number is between 500 and 1000.
A validation rule can also take the value from another field. For example, you could use [EndDate]>=[StartDate] to ensure that the value of the EndDate field is no earlier than the value of the StartDate field for a given record.
You can specify a validation rule by either typing the rule directly into the Validation Rule field, or by using the Expression Builder.
Validation Text
The Validation Text property allows you to provide a message to users when they try to enter invalid data (i.e. data that doesn't pass the valiation rule).
Required Fields
The Required property allows you to specify whether the field is a required field or not (i.e. whether the user can leave the field blank or not).
Show Date Picker
You can use the Show Date Picker property to enable the user to select the date from a date picker. A date picker is a mini pop-up calendar that allows the user to browser to the date and select it by clicking on it.
Note that you can't use a date picker on fields that have an input mask. If you need to use a date picker, you'll need to remove any input masks against that field.
The Expression Builder
The Expression Builder can help you find a suitable expression for a field. It can help you do things like create a validation rule (to ensure data is entered correctly), or specify a default value for a field.
Using the Expression Builder is optional. If you know the expression you need, you can just type it without launching the Expression Builder. However, the expression builder can help you browse for a suitable function, constant, operator, or identifier to assist you in building an expression for the field.
To launch the expression builder, click the ... button at the right side of the property that you wish to apply an expression to.
In the following example, a date/time value of Now() is selected as a default value to use for this field. Now() is an inbuilt function that automatically generates the current date and time.
The Input Mask Wizard
The Input Mask Wizard is a tool that can help you determine an input mask for the field. Similar to the Expression Builder above, the Input Mask Wizard allows you to browse through a list of options.
To launch the Input Mask Wizard, click the ... button at the right side of the Input Mask row. The Input Mask Wizard will launch a small popup on top of the current window.
In the following example, the Input Mask Wizard is being used to set an input mask for the DateDeparture field, which has a data type of Date/Time.
There are many other data types that can benefit from input masks. For example you could use an input mask for phone numbers, social security numbers, addresses, and more.
Your input mask doesn't need to match the way the data is displayed. For example, you could use an input mask to ensure users enter dates as "27/09/1969" but have the dates displayed as "27 Sep 1969".