Using Data Types
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.
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
Long Integer: A long integer is a natural number whose value is between
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.
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.
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.
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:
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.
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:
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.
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:
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.
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.
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:
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:
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:
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.
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.
|Previous||Copyright © 2002-2007 Yevol||Next|