Using Data Types

1. Open the Clarksville Ice Cream database and, from the Tables section, click the New button
2. In the New Table dialog box, click Design View and click OK
3. Set the first Field Name to ScoopsID and press the down arrow key
4. Set the second Field Name to Scoops and press Tab
5. Set its Data Type to Number and press F6
6. In the Field Size combo box, select Byte

Automatic Numbers

If you create a column that will be used to keep an index of records as they are entered and/or deleted, you can set its Data Type to AutoNumber. This would ask Microsoft Access to complete that field with automatically generated numbers for a column whose numbers you don't need to control. This technique is highly used for the first field of a table.

1. The Clarksville Ice Cream database should still be opened with the Table1 in Design View
   In the upper section of the table, click ScoopsID and press Tab
2. Change the ScoopsID Data Type to AutoNumber
3. Close the table to save it. Give it the name Scoops and press Enter
4. When asked whether you want to create a Primary Key, click No
5. Open the Employees table in Design View
6. In the upper section of the table, click EmployeeID and press Tab
7. Click the arrow under the Data Type and click AutoNumber
8. Save the table

Real Numbers

A real number is a number that displays a decimal part. This means that the number can be made of two sections separated by a symbol that is referred to as the Decimal Separator or Decimal Symbol. This symbol is different by language, country, group of languages, or group of countries. In US English, this symbol is the period as can be verified from the Regional (and Language) Settings of the Control Panel:

On both sides of the Decimal Symbol, digits are used to specify the value of the number. The number of digits on the right side of the symbol determines how much precision the number offers.

Microsoft Access provides two types of decimal numbers:

Single: A single is a decimal number whose value can range from –3.402823e³⁸ and –1.401298e^-45 if the number is negative, or from 1.401298e^-45 and 3.402823e³⁸ if the number is positive. To use this type of numbers on a field, set its Field Size to Single.

Double: While the Single data type can allow large numbers, it offers less precision. For an even larger number, Microsoft Access provides the Double data type. This is used for numbers that range from 1.79769313486231e³⁰⁸ to –4.94065645841247e^–324 if the number is negative or from 1.79769313486231E³⁰⁸ to 4.94065645841247E^–324 if the number is positive.

Similar to the relationships among natural numbers, a Single can fit a Double. In fact, you may see that, with experience and if you check the databases (and VBA code) of most programmers, they use the Double most of the time. Although the Double type uses more memory (Is computer memory still expensive? I guess not.), it offers higher precision than the Single, which sets its preference.

Currency Numbers

The Currency data type is used for a column made for monetary values. For such a column, set its Data Type to Currency. During data entry, the user can type a decimal number and press Enter, Tab or click somewhere else. The number would then be converted to represent a monetary value. The number would also display the currency symbol on its left. Like the Decimal Symbol, the character used for currency depends on the country or a group of countries. It can also be verified in the Regional (and Language) Settings of Control Panel in the Currency section.

The Currency data type does not have a Field Size property on database objects. Instead, it uses the Format property to specify how the number would be displayed:

1. Open the Clarksville Ice Cream database and open the Employees table in Design View.
   In the upper section of the table, click Salary and press Tab. Click the arrow of the combo box and select Currency
2. Save the table

Boolean Data Types

Introduction

A datum is referred to as Boolean when it can assume only one of two values. It can be either Yes or No, True or False, On or Off, non-zero or 0. Such a field is appropriate when you want the user to indicate the state of a value.

To specify that a field is Boolean, set its Data Type to Yes/No.

Boolean-Based Options

To support different ways of expressing a Boolean field, Microsoft Access provides various options. After setting the Data Type of a field to Yes/No, you can control how the field would be presented to the user.

The most classic way of representing a Boolean value consists of displaying a check box:

With this layout, the user can either click the check box or give it focus and press the space bar. Alternatively, if you prefer, you can let the user type a value. In this case, after setting the field’s data type to Boolean, in the lower section of the table in Design View, click the Lookup property page and, on the Display Control combo box, select one of the available items:

The default option is Check Box. If you select Text Box, then the user will be able to type True, False, Yes, No, On, Off, 0, or any number. Once the user types one of these values and presses Enter, Tab, or clicks somewhere else, you can control how the field would display the value. To do this, in the lower section of the table in Design View, click the General property page and, in the Format property, select one of the available options:

If you set the Format property to Yes/No and if the user types 0, the field would display No; if the user types any other number, the field would display Yes.

If you set this property to True/False and if the user types 0, the field would display False; if the user types any other number, positive or negative (but it must be a number) the field would display True:

1. The Clarksville Ice Cream database should still be opened with the Employees table in Design View
   In the upper section of the table, click MaritalStatus and press Tab
2. Set its Data Type to Yes/No
3. In the lower section of the table, click the Lookup property page. From the Display Control combo box, select Text Box
4. Click the General tab and, in the Format property, make sure that Yes/No is selected
5. Save the table

Document and Object Linking

Introduction

Object Linking and Embedding (OLE) is a technique that consists of adding in your database (not just database, but our discussion will stop to Microsoft Access databases as if OLE had to do only with Microsoft Access) an object that was created using another application. The technique used to include such an object is particularly easy. Its configuration or involvement can be the subject of speculation. Over all, Microsoft Access does not care much what type of object you want to add to, or include in, your database. It is left to you to decide why you want to include the object and what type of object this would be. Some objects (can tremendously) increase the size of your database.

To use an OLE object in a field, create the data field in the Design View of a table and set its Data Type to OLE Object.

Objects and Data Fields

After setting the Data Type of a field as OLE Object, since the object is external, there are not too many options or properties provided by the table. The application in which you created or will create the object should control it. To actually include an external object into the field, whether using the table Datasheet View or the form in Form View, the user can right-click the field and click Insert Object. This would open the Insert Object dialog box that presents two options to create or select the object:
 

• If you want to use an application installed in your application to create the document, you can click the Create New radio button. Then, in the Object Type list, click the application and click OK
• If the object has been created and resides on a portable media (floppy disk, CD, DVD, etc), on your hard drive or on the network your computer is connected to, you can click the Create From File radio button and click Browse. This would bring the Browse dialog box that allows you to locate and select the document

1. Open the Bethesda Car Rental1 database and open the Cars table in Design View
2. Notice that the Picture field has its Data Type set to OLE Object. Switch the table to Datasheet View
3. Click any cell under the Model column and, on the main menu, click Format -> Freeze Columns
4. Right click Model, select Sort Ascending
5. Scroll to the right to make sure you can see both the Model and the Picture columns
6. Right-click the first empty cell under Picture and click Insert Object…
7. On the Insert Object dialog box, click the Create From File radio button and click the Browse button
8. In the Browse dialog box, using the tree list under the Directories label, locate the folder that contains the resources of our lessons and select it
9. In the File Name list box, click accent1.bmp
    
10. In the Browse dialog box, click Open and, in the Insert Object dialog box, click OK
11. Click the cell under Picture adjacent Cherokee in the Model column. Notice a rectangle box drawn in the cell
12. On the main menu, click Insert -> Object… Click the Create From File radio button and click the Browse button. Notice that the Browse dialog box remembers the last folder used.
13. In the File Name list box, click cherokee.bmp and click Open. Then click OK
14. On the main menu, click Format -> Unfreeze All Columns
15. Close the table. When asked whether you want to save it (the question is stated because the structure of the table was modified when we froze the columns, not because we added the pictures), click No
16. On Database window, click the Forms button and double-click the Cars form to open it
17. Click the Next Record button twice to get to the third car
18. Right-click the white empty area on the top-right section and click Insert Object
     
    
19. On the Insert Object dialog box, click the Create From File radio button and click Browse
20. Navigate to your Exercises folder. From the list of File Names, click grandmarquis2 and click Open
21. On the Insert Object dialog box, click OK
     
    
22. In the same way, add a picture for each car included in the database. If the picture of a car does not appear in the list, skip it. This simply means that there is no picture
23. After using the form, close it

Hyperlinks

As mentioned already, an object created with an external application can be added to a database and the object would be included “as is”. When this is done, Microsoft uses an encryption technique to make the object become part of the database so you do not have to remember to include the object when distributing the database. Because of this, objects such as pictures can highly increase the size of a database. This can be convenient at times. Fortunately, the alternative to this scenario is to provide only a link to the document or file from the database to the external document.

The Hyperlink data type allows you to create a field that, when clicked, would open another document on the same computer, on another connected computer, or on the Internet.

Date and Time

Introduction

Dates and times in Microsoft Access are grouped in a category referred to as Date/Time. Over all, dates and times are considered differently but, to specify that a field would use date, time, or both, set its Data Type to Date/Time. Because there are different ways to display date and time values, the Format property is used to specify how this value should appear. The Input Mask is used to assist the user with data entry.

After specifying that a field will be set for dates or times, you can use the Input Mask property to create mask sections that can assist the user with entering valid dates or times as valid as possible. To support this, you can type a mask in the Input Mask or you can use the Input Mask Wizard, using the characters we reviewed for strings.

1. Open the Clarksville Ice Cream database and open the Employees table in Design View
2. In the upper section of the table, click DateHired
3. In the lower section of the table, click Input Mask and click its ellipsis button
4. On the first page of the Input Mask Wizard, click Short Date and click Next
5. In the second page, accept the suggestion in the Input Mask edit box. Click Next and click Finish
6. Save the table and close it
7. To create a new table, on the Database toolbar, click the arrow of the New Object button and click Table
    
8. In the New Table dialog box, double-click Design View and add the following fields to the table
    

   Field Name	Data Type	Description
   OrderID	AutoNumber	Automatic number
   OrderDate	Text	Date the order was placed
   DayOfWeek	Text	Same as Order Date but displays the name of the day of the week

9. Save the table as DayDateCustomersOrders and decline to create a Primary Key

Dates Formats

After a valid date value has been entered in a field, you can specify how the date would display, not necessary how the date was entered. The Format combo box provides already created formats you can use. These are the most popular formats used for dates. If none of these satisfies you, you can use the following characters or symbols to create a desired format:

To use these letters, simply type the desired combination in the Format field. Here is an example:

This is configured to display a single digit for a day of the month if the day is less than 10, followed by the complete name of the month, followed by the year in 4 digits. During data entry, the clerk can enter a valid date. Once the field looses focus, it displays the date based on the format. Here is an example:

Based on this, you can use any combination of the formats but you should use a combination most regularly used in your language so the users would not be confused.

Besides the indicated characters, you may want to use some other characters to separate them. An example would be January 5, 2004 which uses a comma in the display. To create such sections, include the characters in double-quotes. What you would be doing is to ask Microsoft Access to display such characters “as is” while considering the non-quotes characters as part of the format. Here is an example:

1. In the upper section of the table, click OrderDate and press Tab
2. Set its Data Type to Date/Time and press F6
3. In the lower section of the table, in the Format field, type d mmm yyyy and press Enter. This means that we want the date to display starting with the day of the month, followed by the 3-letter name of the month, followed by the year in 4 digits
4. In the upper section of the table, set the Data Type of the DayOfWeek field to Date/Time and press F6
5. Set the Format to dddd and press Enter
6. Save the table and switch it to Datasheet View
7. Click the empty field under OrderDate. Type 04/12/1998 and press Enter
8. Under DayOfWeek, type 4/12/98 and press Enter
9. Notice that, although both fields received the same date entry, they display it differently
    
10. Switch the table to Design View

Times Formats

Like dates, time values follow the Regional (and Language) Settings of Control Panel when they display. To make this display friendlier, Microsoft Windows provides some characters you can use to format a time:

To control how time values should display in a field, after setting its Data Type to Date/Time, use the Format property. The characters used to create a format are:

When combining these characters to create a format, you should abide by the rules of your language. You should refer to the formula set in the Time property page of the Regional (and Language) Settings of Control Panel. Microsoft Access also refers to it for the character separator. If you want to include any other character, type it in double-quotes.

1. Click the first empty field under DayOfWeek. Type OrderTime and press the down arrow key
2. Type MorningAfternoon and press Tab
3. Type d and press the up arrow key
4. Type d and press F6
5. Click the arrow of the Format combo box and select Short Time
6. Set the Format of the MorningAfternoon field to AM/PM
7. Save the table and switch it to Datasheet View
8. Under OrderTime, type 11:05 and press Tab
9. Under MorningAfternoon, type 11:05 and press Enter. Notice that the display changes to AM
10. Switch the table to Design View

Date and Time Masks

As done with Text based fields, you can use the Input Mask property to assist the user with data entry on date and time-based fields. The idea is to create sections and include character separators in the field to help the user know where a number, a month, a year, an hour, a minute, a second, or the AM/PM can be entered. Once again, you have two main alternatives: using a wizard or creating your own mask.

The easiest way of creating a mask for a date or time-based field is to use the Input Mask Wizard dialog box and follow the steps while selecting the options. You can also use the characters specified earlier to create a custom mask.

1. In the upper section of the table, click OrderDate, press F6 and press the down arrow key
2. Click the ellipsis button of the Input Mask field
3. In the first page of the Input Mask Wizard, click Short Date and press Enter
4. In the second page, accept the default and press Enter twice
5. Click Input Mask to select its value and press Ctrl + C, then click Finish
6. In the upper section, click DayOfWeek and click Input Mask. Press Ctrl + V and save the table
7. In the upper section of the table, click OrderTime and, in the lower section, click Input Mask then click its ellipsis button
8. In the first page of the wizard, click Short Time and press Enter three times
9. Select the Input Mask value and copy it. Then click MorningAfternoon, right-click Input Mask
10. Save the table and switch it to Datasheet View
11. Click the empty field under OrderDate. Type 04/14/1998 and press Enter. Under DayOfWeek, type 4/14/98 and press Enter
12. Notice that, although both fields received the same date entry, they display it differently
     
13. Switch the table to Design View

MOUS Topics

Exercises

1. Open the Yugo National Bank database. Open the Employees table in Design View. Just under Title, add a new Yes/No column named CanCreateNewAccount. Set its Display Control to Text Box. Save and close the table.
2. Open the Employees form in Design View and add the new CanCreateNewAccount field as follows:
    

   Save the form and set this field as follows for the following employees:

   Last Name	Can Create New Account?
   Yuen	1
   Marconi	-8
   Young	208
   Holland	6
   Kast	2500
   Lourde	-34
   Kirkland	-1

   Close the form

3. Open the Customers table in Design View. Change the Data Type of the DateCreated field to Date/Time. Change its Format to Short Date. Use the Input Mask Wizard to set its Input Mask to Short Date. Save and close the table.
4. Open the Customers form in Design View. Delete the DateCreated text and its accompanying label. Using the Field List, add the DateCreated field to where the other was. Save and close the form.
5. Open the Customers form and enter the following values in the Date Created text box
    

   Account Number	Date Created		Account Number	Date Created
   28-3782-8	02/04/2002		92-3782-4	02/04/2002
   38-4227-5	02/04/2002		68-6434-5	02/04/2002
   83-4654-7	02/04/2002		47-4783-2	02/05/2002
   82-3763-2	02/05/2002		72-3474-2	02/05/2002
   34-5458-4	02/05/2002		29-4586-4	02/05/2002
   68-3465-8	02/05/2002		40-4658-6	02/05/2002
   56-8468-5	02/05/2002		94-7785-3	02/08/2002
   37-5764-8	02/08/2002		34-9754-7	02/08/2002
   72-9375-4	02/08/2002		37-5490-6	02/08/2002
   20-3454-9	02/08/2002		76-5475-4	02/08/2002
   27-3457-4	02/08/2002

   Close the form