MS Access provides the following data types:
Text: Text data type is used to store alphabetic, numbers and special characters. It can store up to 255 characters. If length of text field is not specified, the default setting is 50 characters.
Memo: Memo data type is used to store lengthy text. It is normally used to store comments etc. memo fields can hold up to 64,000 characters.
Number: Number data type is used to store numeric data. The fields with Number data type can be used in mathematical calculation. Different types of numeric data types are as follows: 
The Byte, Integer and Long Integer data types cannot store decimal values. If you need to store values with up to four decimal places only, use the Currency data type in preference to Single or Double number fields.
Data/ Time: Date/ Time data type is used to store date and time. MS Access stores date in the standard mm/dd/yy format.
Currency: Currency data type is used to store numbers representing currency. The value in Currency field is rounded to two decimal places. The negative currency values are displayed in bra-chests. The values automatically include a dollar sing ($).
AutoNumber: AutoNumber data type is used to generate the next number automatically when a new record is added. It creates a unique number for each record. The value starts from 1 and is incremented by 1 in each record.
Yes/NO: Yes/No data type is used to store Boolean value. The possible values in this field are True/False of Yes/No.
OLE Object: OLE stands for Object Linking and embedding. This data type is used to enter objects from other applications such as a spreadsheet created in Excel or a picture file.
Hyper Link: The hyper link data type is used to store links to other files, documents or web pages in a field. A hyperlink address has up to four parts.
- The text that appear in the field
- The path to a file or URL
- A sub-address that is a location in the file or page in the website
- The text that appears as the tool tip
Each part is separated by pound sign (#). The syntax of using hyperlink is as follows:
DisplayText # Address # SubAddress # Screen Tip
An example of using hyperlink is as follows:
Google Home Page#http://www.google.com#
#C:\My Documents\ Database.mdb#MyTabble
Which types of formats can be applied on fields in MS Access?
Different types of formats in MS Access are as follows:
Text and Memo format
Diferent formats for text and memo are as follows:
| Symbol | Explanation |
| @ | It indicates a required character or space |
| & | It indicates an optional character or space |
| < | It converts characters to lowercase |
| > | It converts characters to uppercase |
| @;”XYZ” | It displays the text inside double quotes if the user enters no value |
Some examples of formats for text and memo are as follows:
| Format | Datasheet entry | display |
| @@@-@@@@ | 1234567 | 123-4567 |
| @@@-@@@& | 123456 | 123-456 |
| < | HELLO | hello |
| > | hello | HELLO |
| @/! | Hello | Hello! |
| @;”No Data Entered” | (blank) | No Data Entered |
Number Format
Different format for numbers are as follows:
| Format | Explanation |
| 0 | 0 is a placeholder that displays a digit or 0 if there is none. |
| # | # is a placeholder that displays a digit or nothing if there is none. |
| % | % multiplies the number by 100 and added a percent sign. |
Different examples of formats for numbers are as follows:
| Format | Datasheet Entry | Display |
| # # #, # # 0.00 | 123456.78 | 123,456.78 |
| $# # #, # #0.00 | 0 | $0.00 |
| # # #.00% | .123 | 12.3% |
Currency Format
The currency formatting consists of four parts separated by semicolons. These parts are format for positive numbers, format for negative numbers, format for zero values and format for Null values.
| Format | Explanation |
| $# #0.00;($# #0.00)[Red];$0.00;”none” | Positive values will be normal currency format, negative numbers will be red in parentheses, zero is entered for zero values, and “none” will be written for Null values. |
Date Forma
The easiest way to apply a format is to select from drop-down list as follows:
| General dateLong date Medium date Short date Long Time Medium Time Short Time | 06/19/94 5:34:23pmJune 19, 1994 19-Jun-94 06/19/94 5:34:23PM 5:34 PM 17:34 |
The user cans also formats the data according to his particular requirements. Different date formats are as follows:
| Format | Explanation |
| d | It displays 1 or 2 characters for day. Its value can be from 1 to 31. |
| dd | It displays 2 characters for day. Its value can be from 01 to 31 such as 01. |
| m or mm | It displays month as a number. Its value can be from 1 to 12. |
| mmm | It displays month using three characters such as Jan, Feb etc. |
| mmmm | It displays full nam of the month such as January, March etc. |
| /- | It displays separator character. |
| h | It displays hours. |
| n | It displays minutes. |
| s | It displayed seconds. |
Some examples of data formats are as follows:
| Format | Display |
| dddd “,” mmmm d “,” yyyy | Monday, January 1, 2001 |
| ddd “,” mmm “.” d “,”yy | Mon, Jan. 1, ‘01 |
| “Today is” dddd | Today is Monday |
| H:n:s: AM/PM | 12L00:00 AM |
Yes/No Format
The Yes/No fields are displayed as check boxes by default on the datasheet. It can be changed to text box by clicking Lookup tab and changing Display Control to a textbox. The formatting is designated in three sections separated by semicolons. The first section contains nothing but semicolon must be included. The second section specifies formatting for Yes values and third for No values as Follows:
| Format | Explanation |
| ;”Yes”[green];”no”[red] | Prints “Yes” in green or “no” in red |
Write the procedure to apply input mask to a field.
An input mask controls the value of a record and sets it in a specific format. It is similar to Format property but it displays the format on datasheet before data is entered. A phone number field can be formatted with input-mask to accept ten digits as “(555) 123-4567”. The blank field will look like (___) ___ -___.
The following symbols can be used to create an input-mask:
| Symbol | Explanation |
| A | Letter or digit |
| 0 | A digit 0 to 9 without a + or – sign and with blanks displayed as zeros |
| 9 | Same as 0 with blanks displayed as spaces |
| # | Same as 9 with +/- signs |
| ? | Letter |
| L | Letter a through Z |
| C or & | Character or space |
| < | Convert letters to lower case |
| > | Convert letters to upper case |
The following procedure is used to apply input mask to a field:
- Open the table in design view.
- Place the cursor in the field on which the input mask will be applied.
- Click in white space following Input-Mask under General tab.
- Click “…” button. Input-mask wizard will appear.
- Select the required input mask.
- Click Next button.
- Type the required input-mask to change the input mask.
- Select the required placeholder character.
- Click Next button.
- Click Finish button.
What is the procedure of applying primary key MS Access?
Define primary key. What is the procedure of applying primary key MS Access?
The attribute or combination of attributes that uniquely identifies a row or record in a relation is known as primary key. Every record in a table must have a primary key. It differentiates it from all other records in the table. A social security number is an example of a record whose values will only appear once in a table.
A separate field can be added in the table if no existing field produces unique values for every record. MS Access asks the user to create this type of field if the user does not specify primary key while saving the table. MS Access uses the field name ID and data type Auto-number for this field.
The Following procedure is used to specify a primary key:
- Right click the record to be used as primary key. A shortcut menu will appear.
- Select Primary Key from shortcut menu. OR select Edit > Primary Key. The primary key field contains a key image to the left.
