Datatypes, Field Properties, Validation and Masking

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.

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

Currency - Formats money fields.

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 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 e.g. postcode

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

Defualt 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- This is the error message if data is invalid.

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
Unicode Compression- Method of compressing the  data entered into this field.

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

IME Sentance Mode- Allows special character input.

Unic

Method of compressing the  data entered into this field.

Setting Validation Rules

The 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 Text- Message which it will display if the data is unacceptable.

Validation  Rule- Test that will be applied to the data.

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.

>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.

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.

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.

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

0  A number (0-9 must be entered)

#  A number, + or- sign or space may be entered

L  A letter A-Z must be entered

?  A letter A-Z may be entered

A  A letter or digit must be entered

a  A letter or digit may be entered

C  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