Secondary Teaching Resources for I.C.T.
Databases

What is a database?
Access
How to make a new table
Data Types
Input Masks
Data Validation
Lookup values
Primary Keys
Queries
Aggregate Queries
Forms
Reports

Resources

Links

 

 

 

Databases

Input Masks

Unlike manual databases such as a card index system, or a stamp collection, and computer based database has several tools that allow you to check the accuracy, or integrity, of the data as you actually enter it.

There are two main ways in which Access does this and they are called Input Masks and Validation Rules, which are collectively known as Validation Checks

An input mask is a way of making Access check that data fits into a pattern that we specify. A post code follows a set pattern:-

2 letters
+
1 number
+
1 space
+
2 letters
+
1 number


Access has a very sophisticated way of forcing users to input the data correctly, using a pattern of characters that correct data should match.

In the postcode example above, the input mask would be:-

LL0 LL0

You can see from this example that a letter "L" is used whenever we need a letter and a "0" is used whenever we need a number. It isn't obvious, but we have also used a " " so that Access will always place a space in out postcode (N.B. this input mask wouldn't work for some post codes because they only use a single letter at the start, have a look at the table below and see if you can work out the correct input mask for a postcode which may have only one letter at the start - answer at the end of the page)

Here is a list of the characters that we can use in an input mask, and their meanings:-

Mask Character

Meaning
0

Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
L

Letter (A to Z, entry required).
9

Digit or space (entry not required, plus and minus signs not allowed).
A

Letter or digit (entry required).
a

Letter or digit (entry optional).
?

Letter (A to Z, entry optional).
C

Any character or a space (entry optional).
#

A number, a space, or a plus or minus sign can be entered in this position. If the user moves the cursor past this position, Access stores a space.
.

A decimal point must be entered here.
,

Thousand separator
<

Converts all letters that follow to lower case
>

Converts all letters that follow to UPPER CASE
Anything else

Adds the characters to the field, e.g. (000) would allow the user to input three numbers surrounded by brackets.

 

Examples of Input Masks

 

Mask

Acceptable data

Notes
(000) 000-0000
(206) 555-0248
(999) 999-9999
(206) 555-0248
( ) 555-0248

Access will automatically add the brackets and spaces, and will only accept numbers.
(000) AAA-AAAA
(206) 555-TELE

This phone number can also contain words.
#999
–20
2000

The trailing "#" allows a sign to occur at the beginning.
>L0L 0L0
T2F 8M4

 
00000-9999
98115-
98115-3007

The zeros mean the data must use a number, and the nines are optional
>L<??????????????
Maria
Brendan

The trailing letter is a capital and the rest are Lower case.
SSN 000-00-0000
SSN 555-55-5555

The letters SSN will always occur.


Click here to return to the top of the page

Click here to find out about Data Validation

The correct input mask for a postcode would be:-

L?09 LL09

From the table above, we know that L is a compulsary letter and 0 is a compulsary number, whereas ? and 9 are optional letters and numbers.

This gives us:-

One compulsary letter, then one optional one + one compulsary number then an optional one, followed by a space, then two compulsary letters, one compulsary number and one optional one.

Ten out of ten if you followed that, you must be turning into a Pentium 4!

 

Page last updated on October 26, 2002
Contact webmaster, Philip Waud by clicking here