Datatypes, Field Properties, Validation and Masking

By Emma Riley

Data TypesData type Description

Auto Number Automatically generates a number each time you add a record, it is used as a unique identifier for each record.

Text Stores letters, numbers and any combination. Can be up to 255 characters in length.

Memo This is a really big text field/ It can hold up to 64,000 characters.

Number Holds numbers and decimal points but nothing else. Set a field as number if you wish to do calculations.

Currency Formats money fields.

Data Types cont.Data type Description

Date/Time Either the date or the time or even both, in a variety of formats.

Yes/No For fields where one of the two answers is required.

OLE Object You can add photographs, sounds etc. in a field of this data type.

Hyperlink For webpage addresses.

Lookup Wizard Prevent errors by presenting the user with answers to choose from.

Field PropertiesProperty Description

Field Size Used to fix the maximum length of a text field which is 255 characters.

Format This fixes how data can be displayed, for example dates can be displayed in many different forms such as 13/01/01 or 13 jan 01

Input Mask This sets a pattern for the data to be entered into this field.

Caption This is the field label in a form or report.

Field Properties cont.Property Description

Default Value The value entered into the field when the record is created. It is usually left blank but can be very powerful.

Validation Rule This defines the data entry rules.

Validation Text This is the error message if data is invalid.

Required Indicates whether an entry must be made or not.

Indexed This allows data to be stored in the order of this field, which speeds up searches.

Allow zero length Used with text fields to decide whether records in that field are allowed to contain zero length or empty text strings.

Field Properties cont. Property Description

Unicode compression Method of compressing the data entered into this field.

IME Mode Input method editor, which allows special character input.

IME Sentence Mode Allows special character input.

Formats• You can use a format if you want to change the way your data appears on

screen.• Each field type has its own set of formats. Some of the most commonly

used are as follows:

Text and Memo Formats

Symbol Meaning What it does

> Greater than Makes all text appear in upper case

< Less than Makes all text appear in lower case

Formats cont.Currency Formats

In a number field, you can change the Field Size setting from Long Integer to Single if you want to dispense with decimal places. However, if the field is part of a relationship, you should be aware that an AutoNumber field must be set to Long Integer.

General Number Default option

Currency Displays numbers as currency

Fixed Locks the field to display a specified number of decimal places.

Standard Adds a thousands separator.

Percent Displays decimal places as a percentage.

Formats cont.Date/Time formatsYou have a choice of how the date/time is displayed

Yes/No FormatsYou have three initial choices, which are Yes/No, True/False and On/Off. You can add your own selections to this list.

The Default Field PropertyDefault values are added automatically when you add a new record. For example in a table of names and addresses you might set the Town field Brighton. Brighton then appears automatically each time a new record is added and the user can either leave it or change it to something else.

You can also use expressions in this field property. Typically =Date() will return the current date from your PC.

In a Library Book Loaning System the default value for the Date of Loan field could be set to =Date() and , similarly for the Date of Return the default value could be set to =Date()+14 (assuming a 14-day loan period).

Validation and Required FieldsValidation tests and required fields are your weapons in the war against bad data.Using the Required property is easy because there are only two choices – yes and no. Set the Required property to Yes if you want to force the user to enter something in this field.

Setting Validation RulesThe Validation property is more sophisticated because it performs a test on incoming data to make sure it’s what you want.

Two properties require your attention:

Validation Rule Test that will be applied to the data.

Validation Text Message which ACCESS will display if the data is unacceptable.

Validation and Required Fields cont.• Validations work best with number, currency and date fields because the incoming data is

more predictable. An Expression is an instruction that you give to Access in a language that Access can understand. For example, if you want to force users to enter only positive numbers in a number field, the expression is “> 0” i.e. the number must be greater than zero.

Operator Meaning

>0 Must be greater than zero

<>0 Cannot be zero

>=5 Greater than or equal to 5

>=0 And <=100 Must be between 0 and 100

<=0 Or <=100 Must be less than 0 or greater than 100

>=Date() Must be todays date or later

>=Date() Or Is Null Must be todays date or later or blank

>=#1/1/96# And <#1/1/97# Must be a date in 1996

Like “A????” Entry must contain the letter A

• To write a validation rule, you can either write the rule yourself, or you can use the Expression Builder. To start the Expression Builder, click the Build button that appears when you select the Validation Rule Property.

The Expression Builder helps you to write expressions. It lists functions and operators that you can use. It can also prompt you to remind you what to do next and it will stop you making common mistakes.

Example Possible validation text

>8000 Please enter a salary greater than £8000

<#01/01/01# You must enter dates before jan 1st 2001

>Date() The date returned must be after todays date

‘S’ or ‘M’ or ‘L’ Sizes can only be S, M or L

Between 0 and 36 Goals scored cannot be greater than 36

<20 Age of student must be less than 20

IN(‘A’, ‘B’, ‘C’) Grades must be A, B or C

Input Masks

The aim of an Input Mask is to prevent the user entering bad data and to remove data inconsistencies from occurring. The great danger in using them is that you might end up preventing the user from entering correct data. For this reason, you must think carefully before deciding to set an input mask for a field.Input masks make data entry easier. They display on screen a pattern for the data to be entered into the field.An input mask is a series of characters, which tells Access what kind of data to expect. Each field can have an input mask, with the exception of a memo field.They are suitable for data that always has the same pattern s such as phone numbers, national insurance numbers and postal codes.You can either write your own input mask or you can use the Wizard. To use the Wizard, click the Build button, which appears when you click the Input Mask Property.

Characters for input masks you are likely to use are as follows:

0 A number (0-9 must be entered)9 A number (0-9 may be entered)# A number, + or- sign or space may be enteredL A letter A-Z must be entered? A letter A-Z may be entered A A letter or digit must be entereda A letter or digit may be enteredC Any character or space may be entered& Any character or space must be entered< All characters to the right are changed to lower case> All characters to the right are changed to upper case

Examples of Input Masks

A National insurance number in the UK must be of the form AB123456C. All letters are in capitals. Its input mask would be >LL000000L (it must be two letters followed by six numbers and one letter).

A postcode consists of one or two letters, then one or two numbers, then a space, a number and two letters. All the letters must be capital letters. Examples are B1 1BB or DE13 0LL. The input mask would be >L?09 0LL.

Car Registration numbers such as FN03 ANJ would have >LL00 LLL as an input mask.

F2 Select an entire fieldCtrl+; Insert the Current DateCtrl+: Insert the Current TimeCtrl+Enter Insert a Line Break (use it in a memo or large text field)Ctrl++ Add New RecordCtrl+- Delete the Current RecordShift+Enter Save the RecordCtrl+Z Undo the last change you made (don't rely on this)Ctrl+Enter Open the selected object in Design ViewAlt+F4 Quit Access