Tables & Data in Access:

When you are creating a table in Access, you can set up certain requirements for the fields to ease data entry and avoid errors.

The first thing we will look at is default values. A default value is the value that is automatically entered in the field when a new record is created. The user can choose to change the value if the default is inappropriate. Note that default values do not have to be enclosed in quotes when you enter them, unless they can be confused with a command word. Our default value is MA for state, however IN for Indiana or OR for Oregon might present a problem so they can be enclosed in quotes. Notice also that I did not enclose MA in quotes, but they were added.

When you enter a field, the Data type starts out as Text, however there are many other data types that can be used. Note that you can only have one data type per field.

Text

Allows characters, numbers, special characters - can be up to 255 chars in length - set the size in the FieldSize property - Access doesn't reserve space for the portion of the field that is not used

Memo

Lengthy text and numbers up to 65,535 characters

Number

Numeric data used for calculations

Date/Time

8 bytes for date and time values - carries year 100 - 9999

Currency

Currency values/numerics to be used in calculations - 15 whole #s and 4 decimal numbers - 8 bytes

Auto Number

Unique sequential number incremented by 1 or random number - automatically assigned to new record - if you don't establish a primary key, this will be used

Yes/No

Contains Yes/No, True/False or On/Off - 1 bit

OLE Object

Object linked to or embedded in a table - size limit 1 gig

Hyperlink

Text used as a hyperlink address

Lookup Wizard

Microsoft Definition from Help: "Creates a field that allows you to choose a value from another table or from a list of values by using a list box or combo box. Clicking this option starts the LoopupWizard, which creates a Lookup fied. After you complete the wizard, Microsoft Access sets the data type based on the values selected in the wizard. The same size as the primary key used to perform the lookup, tyically 4 bytes."

If you select Number, you can then select the Field Size.

The options are:

 

Formatting Data:

Data formatting should lead to data consistency. Formatting shows how the data is seen, not how the data is actually stored The social security should not have hyphens one time and no hyphens another. The same holds true for phone numbers, item numbers etc. Likewise, alphanumeric data should be in the same case for consistency. Note that if formatting and input masks are used, formatting has precedence.

Formatting on numeric fields can be found under format:

Date/Time also has a variety of features which can be found under format:

You can force your data to be entered in upper case if you put a > in the format for a text field. To force lower case you can put the < symbol. If you make one of these format entries, it not only effects future data, it also effects existing data.

If you use the Yes/No option you can make the selection under format to use Yes/No, True/False, or On/Off.

Let's say you want to format positive numbers using blue, negative numbers using red, 0 values using green and null values using cyan. The custom number format has from one to four sections separated by semicolons. The first is for positive numbers, the second is for negative numbers, the third is for zero values and the fourth is for null values.

The following code would be entered for a number field under format:

$#,###.00[Blue];$#,###.00[Red]; $#,###.00[Green];$#,###.00[Cyan]

This will format as $ followed by a # in the thousands position to set up for commas, then the comma and then ### followed by the decimals. For numbers less than 1000, the comma is suppressed and the $ is floated over next to the first significant digit. Note that numbers like 10000 can be entered successfully. If you want a 0 in the units position if there are no whole numbers, the picture could be $#,##0.00. The colors in brackets indicate the color that will be shown when the field is displayed. Colors that can be used are: Black, Blue, Green, Cyan, Red, Magenta, Yellow, White. You can also display words. For example you can put zero in for zero and null in for the null fields. In this example the words will display in the selected color.

$#,###.00[Blue];$#,###.00[Red];"Zero"[Green];"Null"[Cyan]

In this example, no particular handling is done for the 0 field - they show up as blue.

$#,###.00[Blue];$#,###.00[Red];;"Null"[Cyan]

In this example, the negative numbers show up as blue as do the 0 numbers, however the negative numbers do have a negative sign.

$#,###.00[Blue];;;"Null"[Cyan]

In doing editing, you can use # to display a digit or nothing, a $ to display a dollar sign and a % to multiply by 100 and add the %. Check help for more information.

Input Masks allow for easy data entry because you can set up a mask to show characters in the data while the entry is happening.

The following quote is from Microsoft Help:

"Should I use a data display format or an input mask?

Microsoft Access provides two field properties that produce similar results: the Format property and the InputMask property.

Use the Format property to display data in a consistent format. For example, if you set the Format property for a Date/Time field to Medium Date format, all dates entered will display in this form: 12-Jan-96. If a user of your database enters a date in the form, 01/12/96 (or any other valid date format), Microsoft Access will convert the display to the Medium Date format when the record is saved.

The Format property affects only how a value is displayed, not how it is stored in the table. Also, a display format isn't applied until the data entered is saved, nothing is displayed in the field to suggest or control the format in which data is entered. If you need to control how data is entered, use an input mask in addition to, or instead of, a data display format. If you want data to display exactly as entered, don't set the Format property.

Predefined display formats are available for Number, Currency, Date/Time, AutoNumber, and Yes/No fields, and you can define custom formats for them as well. There are no predefined formats for Text, Memo, or Hyperlink fields, but you can define custom formats. You can't define a display format for OLE Object fields.

Use the InputMask property to display literal display characters in the field with blanks to fill in. For example, if all phone numbers you enter in a field have the same format, you can create an input mask.

An input mask ensures that the data will fit in the format you define, and you can specify the kind of values that can be entered in each blank. For example, the preceding input mask requires that all entries contain exactly enough digits to make up a U.S. area code and telephone number, and that only digits be entered in each blank.

If you define both a display format and an input mask for a field, Microsoft Access uses the input mask when you are adding or editing data, and the Format setting determines how the data is displayed when the record is saved. When using both Format and InputMask properties, be careful that their results don't conflict with each other."

You should note that if you want to use the Input Mask Wizard, you need to have the Advanced Wizards installed. The Wizard is helpful, but it is not necessary if you can figure out the mask you want to use.

For example, I decided that I wanted to put an input mask on the idno so that it would be entered as two characters, followed by a hyphen, followed by one character, followed by a hyphen, followed by two more characters. To do this I put the input mask of 00-0-00 on the idno field. The following illustration shows what is shown after I did this entry and the output results.

Note, the formatting from above with the colors and words for positive, negative, zero and null data is also shown in the output picture.

Using the input mask wizard:

Now I want to work with social security number, I went to my pay database and changed the identification number, which was the empid, to have 9 digits. I will now put on an input mask that will show the data in the format for social security number.

I went to the layout and under input mask clicked the build button (three dots). The screen above appeared. I clicked on Social Security Number. The Try It box will show the format and allow you to try entering data to see if the mask is working the way you want it to. When you click on next, you are asked if you want to alter the mask and especially if you want to alter the place holder - in this case, the place holder is a hyphen. You are then asked how you want to store the data - with or without the hyphens. I choose without.

The input mask that is created looks like this: 000-00-0000;;_

When I displayed the datasheet, I see the hyphens inserted in the correct places in the data.

empid

123-45-1212

234-56-2345

345-67-1234

456-78-5778

567-89-3453

678-90-4534

789-01-4593

890-12-8446

901-23-5468

912-34-7657

923-45-3286

945-67-0843

The explanation of the input mask is that there are three components separated by semicolons.

The InputMask property can contain up to three sections separated by semicolons (;). The first is the mask itself. The second specifies whether the display characters are stored in the table. A 0 means it is stored with the value, a 1 or a blank means it is not - only the data that is entered is stored. The third specifies the character that Access will use to show where you should be keying in data.

Input Masking characters:

0

Digit must be entered (0 to 9) - no + or -

9

Digit or space - entry not required - no + or -

#

Digit or space - entry not required - spaces removed when data saved - + and - allowed

L

Letter - entry required

?

Letters - entry optional

A

Letter or digit - entry required

a

Letter or digit - entry optional

&

Any character or space - entry required

C

Any character or space - entry optional

.,:;-/

Placeholders/separators

<

Conversion to lowercase

>

Conversion to uppercase

!

Input mask will display right to left - characters fill left to right

\

Character following \ will be displayed as literal character (\Z will be Z)

Password

Characters typed in are stored correctly but displayed as *

Validation Criteria:

You can use validation criteria to restrict data entry - to disallow entry of certain types of data by the user. The Validation Rule allows the developer to put in an expression that will test the data for acceptability. The developer can also but in Validation Text to display the error message.

I decided to put a Validation Rule on the DriveNo on the contribu table. The only valid drives are 123, 124, and 125. I went to the Validation Rule for the field and pressed the build button (three dots) to print up the expression builder. Then I entered the validation rule as shown below:



Then I went to the Validation Text and entered the message to display. That is shown below.

Now I want to test my work. When I save, I get a message asking if I want to test current data to meet the validation rules. I said yes. I tested this by changed one of the departments to 127, the message appeared.

Now I decided to work with AmtCont.

I had changed a record 50,000 - I put in a rule that said the amount had to be less than or equal to 2000. When I tried to save it, I was asked about testing current data. I said yes. It came back with an error that existing data did not meet validation requirements. I can then decide how I want to handle this - a list of options is presented.

Requiring Entry:

The required entry property can be set to YES which means that the user must enter something in the field. I set the drive to required and it did not allow me to enter a record without a drive or delete a drive from an existing record.

Indexed:

This allows me to allow or prohibit duplicates through indexing a field.

Creating a lookup column for a list or a combo box:

I am going to do a lookup on the city field and provide a list of cities that can be used. First under city, I selected Lookup Wizard.

I selected the option to type in the values that I want - second option. On the next screen, I accepted the default for number of columns and then entered the city names.

On the next screen I accepted the default name of City. Then I finished. The output is shown below:

The resulting properties in the lookup are shown above. If you want to do it without the wizard, you can enter your own row source and set the properties as needed.