So far we have mainly ignored the Properties section of the table design box, with the exception of the Field Size and Format sections. The Properties section has many other features which are covered here. The list of properties changes depending on the data type chosen.
Remember that you choose the data type for a field in the upper part of the screen and adjust the details for the various data types in the lower part. For example:
Text - has a Field Size component. Choose a size appropriate to the likely data entries, maximum 256 characters.
Choosing Number as the basic data type in the Fields panel leads to various possibilities for field size and format in the Properties section.
| Numeric Size Type | Numeric Range |
| Byte | 8 bits, the smallest, range 0-255 |
| Integer | 16 bits, -32,768 to 32767 |
| Long Integer | 32 bits, -2,147,483,648 to 2,147,483,647 |
| Single | 32 bits, -3.4 x 1038 to +3.4 x 1038 |
| Double | 64 bits, -1.797 x 10308 to +1.797 x 10308 |
| Decimal | 96 bits, -3.4 x 1038 to +3.4 x 1038 with defined precision |
| Replication ID | 16 byte Globally Unique Identifier |
The format of a number varies with the numeric type selected:
| Numeric Format | Explanation |
| General | the data determines the format |
| Currency | symbol and 2 decimal places |
| Euro | Leading Euro symbol included |
| Fixed | at least one digit and 2 decimal places |
| Standard | 2 decimal places and commas |
| Percent | Percentage |
| Scientific | Floating point scientific notation as in 1.797 x 103 |
This allows you to set the number of decimal places in a number. The default (Auto) is 2 decimal places.
For text and some numbers you can define a mask which the user sees and which helps him/her to enter the data. This is used where data has a regular format and is potentially confusing, for example postal codes, insurance numbers and credit card numbers.
Mask definition characters:
| Character | Meaning |
| 0 | single digit |
| 9 | digit or space |
| # | digit or space with + or - sign |
| L | letter |
| ? | letter or space (skip) |
| A | letter or digit |
| a | letter, digit or space (skip) |
| & | character or space |
| C | character or space (skip) |
| . | decimal place holder |
| , | thousands separator |
| :;/ | date and time separators |
| < | convert following characters to lower case |
| > | convert following characters to upper case |
| ! | mask fills from right to left when there are optional characters on the left end of the mask |
| \ | causes next character to be literal |
| "literal" | alternatively, enclose literal text in quote marks |
The input mask includes a number of templates, for example for a short date and the UK postcode. The mask setting for a date in Access is:
00/00/0000;0;_
The last character is the place holder, the character that is displayed before input in completed. This could be changed to '#', for example.
The mask for a postal code provided by Access is:
>LL00\ 0LL;;_
This requires a double digit number before the space but many post codes have a single digit so this is better:
>LA99\ 0LL;;_
The 'A' character allows for single letter postcodes such as those for Liverpool, Birmingham and Manchester and also East, North and West London (E, N, W). The '9' character allows a space to be entered as well as a digit. Postcodes can have 5, 6 or 7 characters plus the optional space so it is quite difficult to make a satisfactory mask.
This works even better:
>Laaaaaaa;;_
The post code begins with a letter and may then consist of up to 7 further characters which may be letters, digits or spaces.
A mask can be used as a type check e.g. to limit phone numbers to digits or as a format check e.g. postcode, National Insurance number. For a phone number:
>999999999999;;_
This property can be used to make a longer or more descriptive field name which will appear on forms and reports (not in datasheet view).
You can enter a default value for most data types except AutoNumber and OLE object. The default for numeric fields is 0, for Yes/No fields it is False.
This can be used to check that data entered into fields lies within specified limits, i.e. a range check. There is nothing to prevent names and many other words being misspelled but short lists can be provided to ensure only valid terms are used and the valid range of numeric values can be controlled. In the music database we might decide to add a validation rule to the Number of Tracks field, setting it to lie between 1 and 30.
To do this open the table in design view, click in the Number of Tracks field and then click in the Validation Rule property box. A build button (dieresis) appears:
Click on the Build button to open the Expression Builder and enter "BETWEEN 1 AND 30" in the white box.
We could use the yellow folders below (Operators/Comparison) but we will leave that until later. Click OK and the rule is entered into the Properties list for that field. Now if we try to enter a value outside the given range a message will be displayed telling us to try again with valid data:
For numeric expressions the normal comparison operators may be used, for example "> 1", "<=30", etc.
We could restrict entry of names to a given list (type something like "Parlophone OR Decca OR Virgin", for example, to restrict data entry to these labels).
With data that cannot be known in advance, such as personal names, there is little that can be done to prevent mistakes, for example 'Suasn' instead of 'Susan'.
For data which follows a specific format wildcards can be used, for example 'LIKE "####"' requires the user to enter exactly 4 digits, 'LIKE "??-??"' requires the user to enter two characters, a hyphen and another two characters.
To create a presence check use 'Is not null' as the validation rule.
Here are some complex validation rules:
Is Null OR NOT Like "*[!a-z]*"
Is Null OR NOT Like "*[!((a-z) or (\))]*"
(Len ([Tutor]) Between 2 AND 5)
(Len ([Tutor]) Between 2 AND 5) AND (Is Null OR Not Like "*[!a-z]*")
((Left
Write in here the text you want to appear when a validation rule is broken.
This is used if a null value is not allowed and the user must enter a value. All primary key fields must have values in their fields. This can be used as a presence check.
This property allows strings to be set to zero length which is distinct from a null value. A null value means that no data has been entered and the value is unknown. This is not the same as a zero length string which is a string where the number of characters is known to be zero. This may seem pedantic but it is an important distinction in relational databases.
An index in a database provides a list of keys which give the position within a file of specific data items. Indexing a field speeds up access to data when a search is performed using that field. Access automatically builds an index for the primary key fields of a table but will only build an index for other fields if this property is set to 'Yes'. You might, for example, decide to index the Club Name and Ground fields of the Clubs table so that you could speed up searches based on those fields.
You will remember that the Category field was defined as a Lookup field so that users could choose from a list when they entered data. Where there is a known list of data items (such as 'pop', 'jazz', etc.) this makes life easier for the user because they don't have to type the item each time and spelling is consistent; the use of a combo box makes it possible for users to enter items not included in the list. It is useful to understand how Lookup works and the properties list gives us some clues.
This can be set to Text Box, List Box or Combo Box. A text box allows free entry of data with no pre-defined list, a list box restricts the user to named items only, a combo box (combination) provides a list and allows free entry as well. The default choice is combo box which you set at the stage in the Lookup Wizard when you chose "I will type in the values that I want".
This can be set to Table/Query, Value List or Field List. In the Lookup wizard you set it to a value list, but you could have chosen a table or query which would return values which exist elsewhere in the database. For short lists it is convenient to type them in but for longer lists which may change it is probably better to get the data from a table - choose the "look up the values in a table or query option" when using the Lookup Wizard.
This may record the name of the table or query from the list of items is derived or it may, if the items were entered from the second option in the Lookup Wizard, record the items themselves. Here is a sample row source which was entered directly:
"pop";"jazz";"reggae";"folk";"country";"blues";"hip hop";"classical";"film";"disco";"garage";"indy"
Note that each item is within double quote marks and separated by a semi-colon. This list can be edited directly, either in the property list box or, more comfortably, in the Zoom box (click in the Row Source property and press Shift-F2). Just make sure you include the quote marks and the semi-colons.
When this property is set to 'Yes' users may only choose a value from the list, they cannot enter a value not in the list.