Now that you have created one or more tables with fields in them, you may wish to focus more closely on the fields. Why? You may want to set limits, place restrictions and tell/show your user how to enter values (or data) into the fields in the correct way or which is standard or easier to understand.
The way you achieve this is through field properties.
Can I set properties to fields?
Yes you can.
Microsoft Access allows each field to have a set of characteristics called properties for determining how to store, handle and display a field's data. What those properties are for each field will depend on the field's data type. For example, you may want a currency field to have a default value of 0. Or you may want a postcode text field to accept a maximum of 4 characters.
If you are going to set properties to fields, make sure you are in Table Design View.
To define properties to fields for how data should be entered or displayed, you have to make rules that tell Microsoft Access what data is valid or how the data should be shown. These rules are called validation rules, input-mark rules and formatting rules.
1. Formatting Rules
Another thing you may wish to investigate when designing your table is the format property. A format is a property you give to a field so it may display and print values in a certain customised way. For example, you may want Microsoft Access to store number values in the format $8.50 instead of 8.5. Or perhaps you want to type a phone number like 0262857000 but want it stored as (02) 6285 7000. You use the format property to achieve this.
To format a field, click the field you want to format in table design mode. A set of field properties will appear below the list of fields. Click into the white box representing the format property. Now the tricky part to this whole exercise is knowing the symbols you should type so you can format the values correctly.
Here is the essential list of symbols and what they do to the values of a field:
TEXT FORMATTING
| Symbol | Value | Format | Result |
| < | James | < | james |
| > | James | > | JAMES |
| & | James | & | James |
| "abc" | 20 | &"lbs" | 20lbs |
| (space) | 20 | & "lbs" | 20 lbs |
| \ | 20 | &\k | 20k |
| @ | 2857500 | @@@-@@@@ | 285-7500 |
| * | Hi | &*! | Hi!!!!! |
| [colour] | Ouch | [Red] | Ouch |
NUMBER FORMATTING
| Symbol | Value | Format | Result |
| # | 2000 | # | 2000 |
| 0 | 2000 | #.00 | 2000.00 |
| . | 2000 | #. | 2000. |
| , | 2000 | #,### | 2,000 |
| $ | 2000 | $#.00 | $2000.00 |
| % | 0.75 | #% | 75% |
| E+ | 2000 | #.0E+00 | 2.0E+03 |
DATE FORMATTING
| Format | Result |
| General Date | 1/1/05 12:35:15 PM |
| Long Date | Saturday, January 1, 2005 |
| Medium Date | 01-Jan-05 |
| Short Date | 1/1/05 |
| Long Time | 12:35:15 PM |
| Medium Time | 12:35 PM |
| Short Time | 12:35 |
2. Input Masks
An input mask field property is the ability to control how a user enters data into a field based on a predefined pattern you create using literal characters and placeholders. Literal characters are something Microsoft Access automatically enters into the field. A placeholder is where the user enters a character of information. The type of placeholder symbol you use will determine whether the user must enter data or not and the type of characters you should use (e.g. a digit only, or a digit and letter, etc).
It is similar to the format property except the user is forced to enter information in a prescribed pattern and helps to minimise errors during data-entry (e.g. accidentally typing extra characters).
INPUT MASK SYMBOLS
| Symbol | Description |
| 0 | Type only a digit between 0 and 9 inclusive (required) |
| 9 | Type only a digit between 0 and 9 inclusive (optional) |
| A | Type a letter or digit (required) |
| a | Type a letter or digit (optional) |
| # | Type a digit or space |
| & | Type any character or space (required) |
| C | Type any character or space (optional) |
| L | Type a letter between A and Z inclusive (required) |
| ? | Type a letter between A and Z inclusive (optional) |
| > | Make the following characters in uppercase |
| < | Make the following characters in lowercase |
3. Validation Rules
Validation rules provide further restrictions to the type of data entered in a field. For example, in an input mask, you may allow a user to enter a digit between 0 and 9 inclusive in a given placeholder, but the entire value of the field containing say two placeholders could end up exceeding 90 (e.g. 99) when in fact you want the value to be 90 or less. This is where you apply validation rules.
Think of validation rules as the means by which you set boundaries to the values given in a field. This might be a range of numbers you are prepared to accept, or whether the user can only type "Yes" or "No" and not any other nonsensical data which could make your database look pretty useless.
To specify a validation rule to a field's properties:
- Select the field you want to change its properties.
- Click the Properties button.
- The Table Properties window will appear.
- Type in a description of the rule in the first line (optional).
- Type an expression in the Validation Rule box which sets conditions for how data is to be entered. If necessary, click on the Expression Builder button that appears inside the Validation Rule box to help you build an expression.
Examples of acceptable validation rules.
- Type an error message in the Validation Text box for Microsoft Access to display to someone who breaks the validation rule.
You will also find other field properties you can modify in the lower half of the Table Design window as well. So experiment with these if you like, or ask Microsoft Access Help for assistance in this area.
