Number Len(String)

Character	Used to enter or accept
0	A single digit
9	A single digit or space
#	A digit, space, + or -
L	An alphabetical character
?	A letter
A	A letter or a digit
a	A letter, a digit, or nothing
&	A character or space
C	A character, space, or nothing
.	A decimal place holder; for US English, this would be the period
,	Thousand separator; for US English, this would be a comma
:;-/	Date and time separator, as specified in the Regional Settings of Control Panel
<	A letter; the letter will be converted to lowercase
>	A letter; the letter will be converted to uppercase
!	Anything; the mask is filled from right to left for this position
|	Anything; the character that follows this one will be displayed itself. For example, if you type |L, the letter L would be displayed instead of being used a mask

Mask	Example 1	Example 2	Example 3
LL	dh	fT	Gm
>LL\-00	WE-47
#0L	8f	16a	04t
>LL\-000	WE-883
000\-000\-0000	265-387-6498
##\-##	02-37	-9-5+	-6-35
&#\-L0\-##	5-u5-00
\(000") "000\-0000	(301) 294-6464
!\(999") "000\-0000""	( ) 392-3873
00\-00\-00\-00	28-73-68-46
>00\-LLLL\-0	78-DRUG-9
>L<LLL	Jean
>L<?????????????	Helene	Antananarivo

Value1 & Value2

"Eating" & " Food"

=Field1 & Field2 & Field_n

= LastName & ", " & FirstName

=[LastName] & ", " & [FirstName]

=[Address] & " " & [City] & " " & [State] & " " & [ZIPCode] & " " & [Country]

LTrim(Expression As String) As String

RTrim(Expression As String) As String

Trim(Expression As String) As String

UCase(Expression As String) As String

LCase(Expression As String) As String

StrComp(Expression1 As String, Expression2 As String, Option) As Number

Left(Expression As String, Length As Number) As String

Left("Republic", 3)

Right(Expression As String, Length As Number) As String

Right("Midnight", 5)

Mid(Expression As String, start As Number, Optional Length) As String

Mid("Ideal", 2)

Mid("Elizabeth", 2, 4)

InStr(Optional start As Number,
      string1 As String,
      string2 As String) As Number

Replace(Expression As String,
	LookFor As String,
	ReplaceWith As String,
	start As Number,
	count As Number,
	Option)

Replace("Elisabeth", "s", "z")

String-Based Controls: The Memo

 

Introduction

The text box we have used to far is made to display only one line of text. In some cases, you may need to display more than one line of text. To support this, Microsoft Access provides the Memo data type. It primarily functions like the Text data type except that it can hold a longer text up to 64000 characters. The text is mostly provided as ASCII. This means that there is no formatting.

To create a text box that can support multiple lines of simple text, if you are working in the Datasheet View of a table:

• Click Click to Add and click Memo in the list that appears
• Click under Click to Add. On the Ribbon, click Fields. In the Add & Delete section, click More Fields and click Memo

To create a Memo field in the Design View of a table, add a new column or click the name of the field. Under Data Type, select Memo.

Practical Learning: Introducing the Memo Type

1. Open the College Park Auto Repair1 database you started in Lesson 15 and continued in Lesson 16
2. In the Navigation Pane, under Tables, double-click RepairOrders to open the table in Datasheet View
3. Click the cell under Year
4. On the Ribbon, click Fields
5. In the Add & Delete section, click More Fields and click Memo
    
   
6. As Field1 is still selected, type Problem Description to replace it and press Enter
7. Right-click the title bar of the form and click Design View
8. Click the first empty box under Field Name
9. Type Recommendations and press Tab
10. In the Data Type, select Memo
11. Click the box corresponding to Description and type: Instructions given to the customer
12. Close the table
13. When asked whether you want to save, click Yes
14. In the Navigation Pane, under Forms, right-click RepairOrders and click Design View
15. On the form, click Order Identification and click it again
16. In the Tools section of the Ribbon, click Add Existing Fields
17. From the Field List, drag Problem Description and drop it in the bottom section of the Order Information tab
     
    
18. Click the Order Summary tab
19. From the Field List, drag Recommendations and drop it on the form
     
    
20. Save the form

Characteristics of a Memo

A memo is primarily a text box. It uses all the characteristics of that control. To be distinguished from a regular text box, the memo uses some unique features. For example, the user can press Enter inside a memo to end a line or a paragraph.

One of the most significant characteristics of a memo is to show one or two scroll bars when necessary. To let you show or hide the scroll bars, the Memo control is equipped with a property named Scroll Bars. To specify it, access the Property Sheet of a memo and click either Format or All. This property has two values. the default is None and means the memory would not display a scroll bar. If you set it to Vertical, when text is being entered in the memo or when the memo is asked to display some text, if the text is too long compared to the area reserved for the memo, it would show a vertical scroll bar. This would allow the user to scroll up and down.

Practical Learning: Adding Scroll Bars to a Memo

1. On the form, click Order Identification and double-click the Problem Description memo
2. In the Property Sheet, click Format
3. Click Scroll Bars, then click the arrow of its combo box and select Vertical
4. On the form, click Order Summary and click the Recommendations memo
5. In the Format tab of the Property Sheet, click Scroll Bars and type v to select Vertical
6. Save the form
7. Close the form

The Rich Text Box

Microsoft Office Access 2010 brought a new data type as the rich text. It allows a text box to display many lines, characters with different colors, words with different fonts, and paragraphs independently formatted.

To create a rich text box in the Datasheet View of a table, click Click to Add. In the list that appears, click Rich Text:

To insert a rich text field, while the table is displaying in the Datasheet View, click a cell under the column header that will precede it. On the Ribbon, click Fields. In the Add & Delete section, click More Fields. In the list, click Rich Text.

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, you can work from the Datasheet View or the Design View of a table.

To apply the OLE type in the Datasheet View of a table, after specifying the column name or clicking a cell under it, in the Data Type & Formatting section of the Datasheet tab of the Ribbon, click the arrow of the Data Type combo box and select OLE Object. If creating a table in Design View, to configure a field that can hold any type of string, specify its Data Type as OLE Object.

Objects and Data Fields

After setting the Data Type of a field to 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. From the resources that accompany these lessons, open the Bethesda Car Rental1 database
2. In the Navigation Pane, under Tables, right-click Cars and click Design View
3. Notice that the Picture field has its Data Type set to OLE Object.
   To switch the table to the another view, right-click its title bar and click Datasheet View
4. Right-click the Model column header and click Freeze Fields
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
8. Click the Browse button
9. In the Browse dialog box, using the tree list under the Directories label, locate the Cars folder that accompany these lessons and select it
10. In the File Name list box, click accent.bmp
11. In the Browse dialog box, click Open
12. In the Insert Object dialog box, click OK
13. On the Ribbon, click Home if necessary.
    In the Record section, click More -> Unfreeze All Fields
14. Close the table
15. 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 picture), click No
16. In the Navigation Pane, under Forms, double-click Cars to open it
17. Click the Next Record button to get to the second 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
20. Click Browse
21. Navigate to your Cars folder. From the list of File Names, click mercgdmarquis.bmp
22. Click Open
23. On the Insert Object dialog box, click OK
24. 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
     
25. After using the form, close it

The Hyperlink

 

Introduction

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 Access 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 inconvenient 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.

Besides the ability to provide a link to a document that could be a picture, you can also create a link to a document that provides more information about a record. You can also create a record that would hold the email addresses of the records on a table. All these can be taken care of through a hyperlink. As described in Lesson 13, a hyperlink a label that, when clicked, would open another document on the same computer, on a document on another connected computer, or on the Internet.

Creating a Hyperlink

To give a column the ability to display records that support hyperlinks, Microsoft Access provides the Hyperlink data type. If working in the Datasheet View

• To create a new field for hyperlinks, click Click to Add and click Hyperlink
• To change the type of a field to support hyperlinks, click a cell under its header. On the Ribbon, click Fields. In the Formatting section, click the arrow of the Data Type combo box and select Hyperlink
• To insert a column that would support hyperlinks, click a cell of the column that will precede it. On the Ribbon, click Fields. In the Add & Delete section, click More Fields and click Hyperlink

To create a hyperlink-based column in the Design View, create a new column or click the name of a column under Field Name. In the Data Type, select Hyperlink.

An Attachment

 

Introduction

The OLE Object provides a convenient means of adding an external file to a record. One of the problems with it is that it can add only one object. Sometimes you want to associate many documents to one record and you may have a different number of objects for each different record. Fortunately, Microsoft Office Access 2007 solves this problem through an attachment. An attachment is a technique of associating one or more files to a record.

Practical Learning: Introducing Attachment Fields

1. From the resources that accompany these lessons, open the Altair Realtors2 database
2. In the Navigation Pane, under Properties, right-click Properties : Table and click Design View

Creating an Attachment Field

To start with an attachment field, you must create a field whose data type is an attachment. You can work in the Datasheet View or in the Design View of a table. To create an attachment in the Datasheet View:

• Click Click to Add. In the list that appears, click Attachment
• To insert a column that would hold attachments, click a cell of the column that will precede it. On the Ribbon, click Fields. In the Add & Delete section, click More Fields and click Attachment

If creating a table in Design View and if you are creating a new column, to configure it to hold one or more attachments, specify its Data Type as Attachment. If a field has already been created and the records have been added to the table, you cannot change the data type of a field to Attachment. You would receive an error when you try to save the table:

In other words, you can specify the Attachment type only if you are creating a brand new field.

After creating an attachment field on a table, you can configure it on a related form. In the Controls section of the Ribbon, the attachment is represented .

Practical Learning: Creating an Attachment Field

1. In the top section of the table, under Field Name, right-click Description and click Insert Rows
2. Set the name to Pictures and the Data Type to Attachment
3. Save and close the table
4. In the Navigation Pane, under Properties: Table, right-click (the) Properties (form) and click Design View
5. In the Controls section of the Ribbon, click Attachment and click the empty right side of the form
6. Move the accompanying label above the newly added control and change its text to Picture(s)
7. If the Property Sheet is not showing, right-click the newly added control and click Properties.
   In the Property Sheet, change the following characteristics:
   Name: atcPictures
   Record Source: Pictures
8. Appropriately position and resize the attachment placeholder as you see fit
    
   
9. Save the form and switch it to Form View

Using an Attachment

Microsoft made a tremendous job with the attachment feature of Microsoft Office Access 2007 also available in the 2010 version. In other words, it is extremely easy to attach a series of objects to a record. You can work either in the Datasheet View of a table or in the Form View of a form. Once the object is displaying, you can either double-click the field or right-click the field and click Manage Attachments. This would open the Attachments dialog box:

In the Attachments dialog box, to create an attachment, you can click the Add button. This would open the Choose File dialog box. It behaves like the Open dialog box. When using it, select the file or object and click Open. The selected object would be added to the Attachments list view. In the same way, you can add the other objects one at a time. Alternatively, to add many objects, in the Choose File dialog box:

1. To select objects one at a time:
   1. Click one of the objects to add
   2. Press and hold Ctrl
   3. Click each object to add, one object at a time
   4. After selecting the desired objects, release Ctrl
   5. Click Open
2. To select objects in a range:
   1. Click one object at one end of the range
   2. Press and hold Shift
   3. Click the object at the other end of the range
   4. Release Shift
   5. Click Open
3. To select objects in a range:
   1. Click somewhere on the left (or the right) or the top (or the bottom) side of an object at one end of the range and hold the mouse down
   2. Drag in the opposite direction to draw a fake rectangle that would touch or include the objects to be added
   3. When the objects have been included in the range, release the mouse
   4. Click Open

To remove an item from the collection, click it in the Attachments list box and click Remove. Once you are ready with the list of attachments, click OK.

After the attachments have been created, to access those of a record, navigate to that record. Then, right-click the placeholder of the attachment. A menu would come up. Among other menu items, the menu has a Forward and a Back options:

• If the record has only one attachment, both menu items would be disabled
• If the record has only two items, when you access the record, the Forward menu item would be enabled and the Back option would be disabled. You can click Forward to access the next object. At this time, the Forward menu item would be disabled and the Back option would be enabled. You can click Back to access the previous object
• If the record has more than two items, when you access the record, the Forward menu item would be enabled and the Back option would be disabled. You can click Forward to access the next object. You can keep clicking Forward to advance in the collection. You can click Back to access the previous object. From the second object to the one before the last, both the Forward and the Back menu items would be enabled. When you get to the last object of the collection, the Forward menu item would be disabled and the Back item would be enabled

Practical Learning: Attaching  Objects to a Record

1. For each record, right-click the placeholder under Picture(s) and click Manage Attachments...
2. In the Attachments dialog box, click Add...
3. From the resources that accompany these lessons, inside the Houses folder, locate a file whose 6 (first) characters are the same as the property number.
   If the file name does not end with a, such as 475974.bmp, click it:
    
   
   
   If the file name ends with a, such as 524880a.bmp, click it and click Open. Then click Add again and add additional files with the same 6 digits in the name and that ends with a letter such as b, c, etc
    
   
4. After adding the picture(s), click OK
5. Navigate to each record. Right-click the picture and review the pictures attached to each record
    
   

6. Close the form
7. Close Microsoft Access

Exercises

 

Yugo National Bank

1. Open the Yugo National Bank1 database
2. Open AccountTypes table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   AccountTypeID	AutoNumber		Account Type ID
   AccountType	Text	50	Account Type
   Description	Memo

3. Save and close the table
4. Open TransactionTypes table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   TransactionTypeID	AutoNumber	Long Integer	Transaction Type ID
   TransactionType	Text	50	Transaction Type
   Description	Memo

5. Save and close the table
6. Open Customers table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   CustomerID	AutoNumber		Customer ID
   AccountNumber	Text	20	Account Number
   CustomerName	Text	50	Customer Name
   Address	Text	60
   City	Text	50
   State	Text	50
   ZIPCode	Text	20	ZIP Code
   Country	Text	50
   HomePhone	Text	40	Home Phone
   WorkPhone	Text	40	Work Phone
   WorkExtension	Text	20	Extension
   EmailAddress	Hyperlink		Email Address
   Notes	Memo

7. Save and close the table
8. Create a form for the AccountTypes table. Save the form as AccountTypes and design it as you see fit before closing it. Here is an example:
    
   
9. Create a form for the TransactionTypes table. Save the form as TransactionTypes and design it as you see fit before closing it. Here is an example:
    
   
10. Create a form for the Customers table. Save the form as Customers and design it as you see fit before closing it. Here is an example:
     
    

Watts A loan

1. Open the Watts A Loan1 database
2. Open the Employees table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   EmployeeID	AutoNumber		Employee ID
   FirstName	Text	50	First Name
   LastName	Text	50	Last Name
   Title	Text	80
   WorkPhone	Text	40	Work Phone
   Address	Text	100
   City	Text	50
   State	Text	50
   ZIPCode	Text	20	ZIP Code
   Country	Text	50
   HomePhone	Text	40	Home Phone
   Notes	Memo

3. Save and close the table

World Statistics

1. Open the World Statistics1 database
2. Open the Countries table in to Design  and change it as follows:
    

   Field Name	Data Type	Field Size	Caption
   CountryID	AutoNumber		Country ID
   CommonName	Text	50	Common Name
   InternetCode	Text	10	Internet Code
   LongName	Text		Long Name
   GovernmentType	Text	60	Government Type
   WebSite	Hyperlink		More Information

3. Save and close the table

US Senate

1. Open the US Senate1 database 
2. Open the Senators table in Design View  and change it as follows:
    

   Field Name	Data Type	Field Size	Caption
   SenatorID	AutoNumber		Senator ID
   SenatorName	Text	50	Senator Name
   SeatingStatus	Text	50	Seating Status
   Gender	Text	40	Last Name
   State	Text	2
   Race	Text	40
   Religion	Text	50

3. Use the Lookup Wizard on the State to create a list of the US states using the abbreviation for each
4. Use the Lookup Wizard on the Race column to create a list of the races. The options will be Black, White, Native American, Hispanic, Asian, Other
5. Use the Lookup Wizard on the Religion to create a list of the religions. The options will be Catholic, Muslim, Jewish, Baptist, Presbyterian, Atheist, Other
6. Save and close the table