Home

Using Data Types
 

Numbers

 

Introduction

A number is a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), a combination of digits, or a combination of one or more digits, a separator, and one or more digits. Microsoft Access provides three techniques or categories of numbers. These should be applied appropriately to make your database as effectively as possible. To specify that a field will contain only numeric values, after displaying the table in Design View and selecting the field under Field Name, set its Data Type to Number, Currency, or AutoNumber.

When reviewing strings, we saw that the Field Size of a Text data type is used to specify the number of characters of the string. Numeric data types also use the Field Size property. This time, this property is used to control the type or range of numeric values that would be accepted or excluded from the field. While the Data Type column allows you to select the category of number, the Field Size field actually specifies the (Microsoft Visual Basic) data type that will (programmatically) be applied to the field.

Natural Numbers

A natural number is one that contains only one digit or a combination of digits and no other character, except those added to make it easier to read. Examples of natural numbers are 122, 8, and 2864347. When a natural number is too long, such as 3253754343, to make it easier to read, the thousands are separated by a special character. This character depends on the language or group of languages and it is called the thousands separator. For US English, this character is the comma. The thousands separator symbol is mainly used only to make the number easier to read. You should be careful to inherently use it in your database. Fortunately, your users will be trained to deal with it.

To support different scenarios, Microsoft Acess provides different types of natural numbers:

Byte: A byte is a small but positive natural number that ranges from 0 to 255. This type of number is applied to such items as a person's age, the number of students in an elementary classroom, the number of rooms in a regular house, the number of pages of a magazine or newspaper, etc. If you are creating a field that will need this range of values, after setting its Data Type to Number set its Field Size to Byte.

Integer: An integer is a natural number larger than the Byte. It can hold a value between 
-32,768 and 32,767. Examples of such ranges are: the number of pages of a book. For a field that would hold this range of values, set its Field Size to Integer.

Long Integer: A long integer is a natural number whose value is between 
–2,147,483,648 and 2,147,483,642. Examples are the population of a city, the distance between places of different countries, Number of words of a book. For such a field, set its Data Type to Number and its Field Size to Long Integer.

In our examples, we were giving types of fields for each kind. It is important to note that, since a long integer can hold larger numbers than the integer, anything applied as an integer can also be used as a long integer. For example, you can use a long integer to represent the number of pages of a book, even if the books in a collection are not expected to hold more than 1500 pages. In the same way, since an integer is larger than a byte, anything that fits in a byte can also be used as an integer. Based on this, an integer can be used to represent the categories items in a collection, even if the items are not expected to be higher than 200. For example, you can use an integer to count the number of keys on a computer keyboard, knowing that this number is expected to stay below 148.

Practical Learning: Using Natural Numbers on Fields
  1. Open the Clarksville Ice Scream 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 make it a 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 field 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 field whose number you do not need to control. This technique is highly used for the first field of a table.

Practical Learning: Using an Automatic Number
  1. The Clarksville Ice Scream 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

Employees

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 of computers of most regular users:

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.402823e38 and –1.401298e-45 if the number is negative, or 1.401298e-45 and 3.402823e38 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.79769313486231e308 to –4.94065645841247e–324 if the number is negative or from 1.79769313486231E308 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, it offers higher precision than the Single, which sets its preference.

Currency Numbers

The Currency data type is used for a field made for monetary values. For such a field, 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 money 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.

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:

Practical Learning: Using the Currency Data Type
  1. The Clarksville Ice Scream database should still be opened with the Employees table is 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 data field 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 various 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 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 Yes/No, 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 press 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:

Practical Learning: Using Boolean Field
  1. The Clarksville Ice Scream 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. 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
 

Practical Learning: Using OLE Objects
  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. Scroll to the right to make sure you can see both the Model and the Picture columns
  5. Right-click the first empty cell under Picture and click Insert Object…
  6. On the Insert Object dialog box, click the Create From File radio button and click the Browse button
  7. In the Browse dialog box, using the tree list under the Directories label, locate the folder that contains the resources of this ebook and select it
  8. In the File Name list box, click accent.bmp
     
  9. In the Browse dialog box, click Open and, in the Insert Object dialog box, click OK
  10. Click the second cell under Picture, the one that is empty. Notice a rectangle box drawn in the cell
  11. 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.
  12. In the File Name list box, click cherokee.bmp and click Open. Then click OK
  13. On the main menu, click Format -> Unfreeze All Columns
  14. 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
  15. On Database window, click the Forms button and double-click the Cars form to open it
  16. Click the Next Record button twice to get to the third car
  17. Right-click the white empty area on the top-right section and click Insert Object
     
    Insert Object
  18. On the Insert Object dialog box, click the Create From File radio button and click Browse
  19. Navigate to your Exercises folder. From the list of File Names, click escape and click Open
  20. On the Insert Object dialog box, click OK
     
  21. 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
  22. 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.

Practical Learning: Setting Date/Time Formats
  1. Open the Clarksville Ice Scream 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 CustomersOrders 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. Those are the most popular formats use for dates. If not of those satisfies you, you can use the following characters or symbols to create a desired format:

Format Used For Used to Display
d Days The day as a number from 1 to 31
dd Days The day as a number with a leading 0 if the number is less than 10
ddd Weekdays The name of a weekday with 3 letters such as Mon, Tue, etc
dddd Weekdays The complete name of a week day such as Monday, etc
w Week The numeric day of the week such as 1
ww Week The numeric week of the year, ranging from 1 to 53
m Months The numeric month from 1 to 12
mm Months The numeric month with a leading 0 if the number is less than 10
mmm Months The short name of the month such as Jan, Feb, Mar, etc
mmmm Months The complete name of the month such as January, February, etc
q Quarters The numeric quarter of the year
yy Years Two digits for the year as 00 for 2000 or 03 for 2003
yyyy Years The numeric year with 4 digits

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:

Practical Learning: Setting Date/Time Formats
  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 usually 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:

Format Used For Used to Display
: Separator The character separator for time values
This character is set in the Regional (and Language) Settings of Control Panel 
h or H Hours An hour number from 0 to 23
If the hour is less than 10, it would display without the leading 0
hh or HH Hours An hour number from 0 to 23
If the hour is less than 10, it would display with the leading 0 such as 08
n or N Minutes A minute number from 0 to 59
If the number is less than 10, it would display without the leading 0
nn or NN Minutes A minute number from 0 to 59
If the number is less than 10, it would display with the leading 0 such as 06
s or S Seconds A second value from 0 to 59
If the number is less than 10, it would display without the leading 0
ss or SS Seconds A second value from 0 to 59
If the number is less than 10, it would display with the leading 0 such as 04
ttttt   The time using the formula of the Long Time of the Regional Settings of Control Panel
am/pm AM and PM am (in lowercase) if the time is configured to display in the standard (non military time) and if the time occurs in the morning, or pm (in lowercase) if the time is configured to display in the standard (non military time) and if the time occurs in the afternoon
AM/PM AM and PM AM (in uppercase) if the time is configured to display in the standard (non military time) and if the time occurs in the morning, or PM (in uppercase) if the time is configured to display in the standard (non military time) and if the time occurs in the afternoon
a/p AM and PM a (in lowercase) if the time is configured to display in the standard (non military time) and if the time occurs in the morning, or p (in lowercase) if the time is configured to display in the standard (non military time) and if the time occurs in the afternoon
A/P AM and PM A (in uppercase) if the time is configured to display in the standard (non military time) and if the time occurs in the morning, or P (in uppercase) if the time is configured to display in the standard (non military time) and if the time occurs in the afternoon
AMPM AM and PM The AM-PM character. Microsoft Access would refer to the format set in the Regional (and Language) Settings of Control Panel

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.

Practical Learning: Using the Time Data Type

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

Practical Learning: Creating Date and Time Masks

  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
  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 and click Input Mask. Paste the
  10. Save the table and switch it to Datasheet View
  11. Click the empty field under OrderDate. Type 04/12/1998 and press Enter. Under DayOfWeek, type 4/12/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
 

Lesson Summary
 

MOUS Topics
S1 Determine appropriate data inputs/outputs for your database
S12 Use multiple data types
S44 Add Hyperlinks
 

Exercises
 

Yugo National Bank

  1. Open the Yugo National Bank database. Open the Employees table in Design View. Just under Title, add a new Yes/No field 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
    Yound 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
 
Previous Copyright © 2002-2007 Yevol Next