Strings

Introduction to Strings

Text on a Table

A string is text made of a readable letter, a symbol, a word or a group of words.Microsoft Access supports two broad types of text actually based on their length. A short text is one that has a maximum size of 255 characters. To support this, Microsoft Access provides a data type named Short Text. To specify that a column or field will use a short text:

• In the Datasheet View of a table
  • Click Click to Add and select Short Text
  • Click a cell in the design column, in the Add & Delete section, click the Short Text button
• In the Design View, after setting a field name, specify its Data Type as Short Text:

  

To assist you with common text fields, Microsoft Access provides many options in the Datasheet View. To use one of them, on the Ribbon, click Fields, in the Add & Delete section, click More Fields. In the Quick Start section, click the desired option.

If some columns exist already, to insert a text-based field between two columns, click the column header, or a cell under the column, that will precede it. On the Ribbon, click Fields. If a column has been created already and it is using a data type other than Short Text, to change it, click its column header or a cell under its column. On the Ribbon, click Fields. In the Formatting section, click the arrow of the Data Type combo box and select Short Text.

Practical Learning: Introducing Strings

1. Start Microsoft Access
2. In the list of files, click Altair Realtors1 from Lesson 17
3. In the Navigation Pane, double-click the Properties table to open it
4. On the table, click any cell under Property Type
5. On the Ribbon, click Fields
6. In the Add & Delete section of the Ribbon, click More Fields and click Address
7. On the table, right-click Country Region and click Delete Field

Text on a Form

As we have seen in previous lessons, after creating text-based field, you can add it to a form or report, in which case it would be used as a text box. A text-based column can also be used in a form or report as a combo box or any text-based control.

To access the string stored in a text box or any text-based control, use only the name of the control (don't add .Text)

Practical Learning: Using Text on Windows Controls

1. In the Navigation Pane, right-click the Properties form and click Design View
2. In the Property Sheet, click Data and change the Control Source of each control as follows:
    

   Control Name	Control Source
   Address	Address
   City	City
   State	State
   ZIPCode	ZIPCode

3. Close the form
4. When asked whether you want to save, click Yes

Text as a Value

To indicate or specify the value of a string, put it between double-quotes.

Introduction to Strings and Expressions

As mentioned in the previous lesson, you can create an expression-based column on a table. The expression can include only constant values, only constant strings, only columns names, or a combination of those.

Practical Learning: Creating and Using Text-Based Expressions

1. On the Ribbon, click File and click Open
2. In the list of files, click FunDS1
3. In the Navigation Pane, double-click the Employees table to open it in the Datasheet View
4. On the form, click the empty cell under Last Name
5. On the Ribbon, click Fields
6. In the Add & Delete section, click More Fields, position the mouse on Calculated Field, and click Text

   

7. In the middle list of the Expression Builder dialog box, double-click FirstName
8. Type & " " &
9. In the middle list, double-click LastName

   

10. Click OK
11. Type EmployeeName in place of Field1
12. Click any cell under EmployeeName
13. In the Properties section of the Ribbon, click Name & Caption
14. Set the Caption as Employee Name and click OK
15. Close the table
16. In the Navigation Pane, right-click the Employees form and click Design View
17. In the Tools section of the Ribbon, click Add Existing Fields
18. From the Field List, drag EmployeeName and drop it on the form
19. Format its design to be like the other text boxes
20. Close the form
21. When asked whether you want to save, click Yes
22. In the Navigation Pane, right-click the ShoppingSessions form and click Design View
23. In the Controls section of the Ribbon, click the Combo Box control and click in the Detail section form
24. In the first page of the wizard, make sure the top radio button is selected and click Next
25. In the second page of the wizard, make sure Table: Employees is selected and click Next
26. In the Available Fields of the third page of the wizard, double-click EmployeeName and click Next
27. Don't change anything in the fourth page of the wizard and click Next
28. Don't change anything in the fifth page of the wizard and click Next
29. In the sixth page of the wizard, click the arrow of the combo box and select EmployeeNumber
30. Click Next
31. In the seventh page of the wizard, set the label as Processed By:
32. Click Finish
33. Close the form
34. When asked whether you want to save, click Yes
35. On the Ribbon, click File and click Open
36. In the list of files, click Ceil Inn1 from the previous lesson
37. In the Navigation Pane, double-click the Employees table to open it in the Datasheet View
38. On the form, click the empty cell under Last Name
39. On the Ribbon, click Fields
40. In the Add & Delete section, click More Fields, position the mouse on Calculated Field, and click Text

    

41. In the middle list of the Expression Builder dialog box, double-click FirstName
42. Type & " " &
43. In the middle list, double-click LastName

    

44. Click OK
45. Type EmployeeName in place of Field1
46. Close the table
47. In the Navigation Pane, right-click the Employees form and click Design View
48. In the Tools section of the Ribbon, click Add Existing Fields
49. From the Field List, drag EmployeeName and drop it on the form
50. Format its design to be like the other text boxes
51. Close the form
52. When asked whether you want to save, click Yes

Primary Charactistics of Strings

The Size of a String

The size of a string is the number of characters it contains or may hold. By default, when you have just created a Short Text-based field, whether in the Datasheet View or Design View, its default size is automatically set to 255 characters. In some circumstances, you may want the field to deal with a different size. You can therefore decrease or increase this size for any number between 1 and 255:

To specify the allowable number of characters of a Short Text-based field:

• In the Datasheet View, click a cell in the target column. On the Ribbon, click Fields. In the Properties section, click Field Size and change the value:

• In the Design View, in the top section, click the name, data type, or description of the column. In the lower section of the table, click Field Size and type the desired number.

As mentioned already, the default size of a Short Text-based field is set to 255. This value is set in the Access Options. To change it, open the dialog box (on the Ribbon, click File and click Options). In the Default Text Field Size spin button, enter the desired value:

After setting the Field Size property, the database would make sure that the user can only type so many characters.

Practical Learning: Setting Field Sizes

1. In the Navitation Pane, double-click the Properties table to open it in Datasheet View
2. On the table, click any cell under Address
3. On the Ribbon, click Fields
4. In the Properties section of the Ribbon, in the Field Size, click the value, type 100 and press Enter
5. Read the message box and click Yes
6. Right-click the tab of the table and click Design View
7. In the top section of the window, change StateProvince to State
8. In the top section of the window, change ZIPPostal to ZIPCode
9. Complete the fields as follows:
    

   Field Name	Field Zide	Caption
   PropertyNumber		Property #
   Address	100
   City	40
   State
   ZIPCode		ZIP Code
   PropertyType
   Condition
   FinishedBasement
   Pictures

10. Close the table
11. When asked whether you want to save, click Yes

The Length of a String

To get the size of a string, you can call a function named Len. Its syntax is:

Len(String) As Number

When calling this function, pass a string etween double-quotes or the name of a control. The function then produces the number of characters in it.

Practical Learning: Setting the Maximum Size of a Short Text Field

1. On the Ribbon, click File and click Open
2. In the list of files, click College Park Auto Repair1 from Lesson 16
3. In the Navigation Pane, right-click the JobsPerformed table and click Design View
4. In the top portion of the window, click JobName
5. In the bottom part, change the following characteristics:
   Field Size: 100
   Caption: Job Name
6. Save and close the table
7. In the Navigation Pane, right-click the PartsUsed table and click Design View
8. In the top portion of the window, click PartName
9. In the bottom part, change the following characteristics:
   ield Size: 80
   Caption: Part Name
10. Save and close the table
11. In the Navigation Pane, right-click the RepairOrders table and click Design View
12. Specify the fields sizes as follows:
     

    Field Name	Field Size
    CustomerName	50
    PhoneNumber	20
    Address	60
    City	40
    State	40
    ZIPCode	20
    Make	40
    Model	40

13. Save and close the table

Input Masks

A mask is a technique of creating sections, also called placeholders, in a field. A section can be configured to accept only a letter, only a digit, a character or a digit, any symbol, nothing, or a particular character that the user cannot change. Microsoft Access supports two primary categories of masks: implicit and explicit. An implicit mask is one that is comtrolled by a data type. This means that, if you set a certain data type to a field, Microsoft Access can make sure that only valid values can be entered into the field.

An explicit mask is a field that is made to control what value is entered into it. When creating the mask, you will use some predefined characters and create a combination of your choice. A mask, or the type of mask, depends on the data type of the field.

You can create the mast for a field of a table or for a text box in a form. If you create the mask for a field in a table, when that field is added to a form, the mask will apply. If you change the mask of an existing field of a table after that field has been added to a form, the text box of that field will not be update on the form. You can either delete the text box on the form and re-add it to the form, or you can create the mask on the form.

To create a(n explicit) mask on a field:

• Display the table in Design View. In the top section of the window, click the field that needs the mask and specify its type. In the lower section of the window, click Input Mask
• Display the form in Design View. On the form, click the text box that needs the mask. In the Data tab of the Property Sheet, click Input Mask

In both cases, the field will be equipped with a browse button: .

Before creating a mask for a field, Microsoft Access comes with various pre-defind masks you can apply to a field. Masks are available for dates, times, US Social Security Number, currency values, etc. To help you apply one of these masks, Microsoft Access is equipped with the Input Mask Wizard. To open it, click the Browse button.

The first page of the wizard displays the most regularly used masks, including those for US/Canada telephone number, US Social Security #, US ZIP code, date, time, etc:

If you see a mask that suits you, you can check it by clicking the Try It text box. This would show the placeholders for the sections of characters that could be entered during data entry. If you see a mask you want to use, you can click it and click Next. If none of the masks suits your need, you can create a new one and add it to the list. To do this, click the Edit List button. This would open the Customize Input Mask Wizard dialog box:

By default, the Customize Input Mask Wizard offers four already created masks, including US/Canada phone number, US Social Security Number, US ZIP Code, and telephone extension number. You can check them by click the Next Record button in the bottom section of the dialog box. To create a new mask, click the New (Blank) Record button . After creating a new mask, you can click Close.

To complete the mask, continue with the wizard. If none of the masks provided by the wizard suits you, you can create your own. To do that, click the Input Mask property for the desired fields and uses the following characters to create the mask:

If you want to include a word or sentence as part of the mask, type it in any section as desired.

Practical Learning: Using Input Masks

1. Re-open the Altair Realtors1 database
2. Display the Properties table in Design View
3. In the top section of the window, click State
4. In the lower section, click Input Mask, type >LL and press Enter
5. In the upper section of the window, click ZIPCode
6. In the lower section, click the Input Mask field and click its ellipsis button
7. When asked whether you want to save the table, click Yes
8. In the first page of the wizard, click ZIP Code
9. Click the Try It text box:

   

10. Click Next

    

11. Click Next
12. Click the first radio button

    

13. Click Next
14. Click Finish
15. Right-click the tab of the table and click Datasheet View
16. When asked whether you want to save the table, click Yes
17. Update the following records:
     

    Property #	Address	City	State	ZIP Code
    524880	1640 Lombardo Ave	Silver Spring	md	20904-1649
    688364	10315 North Hacht Rd	Alexandria	va	22231
    611464	6366 Lolita Drive	Laurel	md	20707-1807
    749562	495 Parker House Terrace	Gettysburg	wv	26901-1101
    427115	10232 Truesome Drv	York	pa	17401
    200417	4140 Holisto Crt	Germantown	md
    927474	9522 Lockwood Rd	Arlington	va	22203-8212
    682630	6114 Costinha Avenue	Martinsburg	wv	25401-4240
    288540	10340 Helmes Street #408	Silver Spring	md	20906-8006

18. Close the table

SQL and Strings

The SQL supports strings in two data types: CHAR, TEXT, and VARCHAR. They are the same as Microsoft Access's Short Text data type. Here are examples of using them:

CREATE TABLE Contractors
(
    FirstName CHAR,
    LastName TEXT,
    Gender CARCHAR
);

The char or the text data types are used for columns whose fields would receive (or present) text of 1 to 255 characters. If you want the field to hold a maximum of less than 255 characters, you must add parentheses to the data type and enter the maximum number in the parentheses. Here are examples:

CREATE TABLE Persons
(
    FirstName     CHAR(20),
    MiddleInitial Char(1),
    LastName      TEXT(20),
    Title         text(50),
    Gender        VARCHAR(20),
    Address       varchar(200)
);

Operations on Strings

String Concatenation

The & operator is used to add one string to another. It can also be used to append the contents of two controls or two expressions. This is considered as concatenating them. The general formula to follow is:

Value1 & Value2

The values on both sides can be constant values. Here is an example:

"Eating" & " Food"

One value can be the name of a field and the other a constant value or both values can be the names of fields. Just as you can involve two values in a concatenation, you can also use more than one. To concatenate more than two strings, use as many & operators between any two values or expressions as necessary.

After concatenating the strings or values, you can assign the result to another value or expression using the assignment operator. The formula to follow is:

=Field1 & Field2 & Field_n

Here is an example:

= LastName & ", " & FirstName

Once again, you should include the name of a field in square brackets:

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

Here is another example:

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

This would display a complete address in a field.

Trimming a String

Trimming is an operation that gets rid of leading or ending spaces in a string. To remove any (empty) space on the left side of a string, call a function named LTrim. Its syntax is:

LTrim(Expression As String) As String

The original string is passed as argument to the function. The function then removes any empty space from the left of the string and then the function returns the resulting string (without any space from the left section). If the original string does not have any leading space, the function would return the same string.

To remove any space on the right side of a string, call a function named RTrim. Its syntax is:

RTrim(Expression As String) As String

To remove empty spaces on both the left and the right sides of a string, call a function named Trim. Its syntax is:

Trim(Expression As String) As String

The string is passed to the function. The function then removes empty spaces, if any, on each side, and the function returns the new string.

Character Case Conversion

To convert a lowercase character or string to uppercase, call a function named UCase. Its syntax is:

UCase(Expression As String) As String

To convert a character or a string from uppercase to lowercase, call the LCase() function. Its syntax is:

LCase(Expression As String) As String

String Comparisons

String comparison allows you to find out which one of two strings is longer or whether both strings are equal. To compare two strings, call a function named StrComp. Its syntax is:

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

The function takes two strings and an option as arguments. It then compares the strings:

• If the strings are the same, the function returns the number 0
• If the first string is shorter than the second, the function returns the number -1
• If the first string is longer than the second, the function returns 1
• If one of the strings is null, the function returns null

The third argument is optional, which means you don't have to provide it. If you want to influence the way the comparison is made, you can pass the third argument with one of the following values: -1, 0, 1, 0r 2.

Sub-Strings

To create a string that is made of one or more characters from the left side of a string, call the Left() function. Its syntax is:

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

This function takes two arguments. The first argument is a string. The second argument specifies the number of characters to be considered from the first argument.

To create a string that is made of one or more characters from the right side of a string, call the Right() function. Its syntax is:

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

To create a string that is made of one or more characters from anywhere in a string, call the Mid() function. Its syntax is:

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

Locating a Character or a Sub String

You can analyze a string and find out whether it contains a certain character or a sub string. If it does, you can get the position of the character or the sub-string and use that information as you see fit. To perform this operation, call a function named InStr. Its syntax is:

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

This function takes 3 arguments with 2 required. The second argument is the string to be examined. In it, the function would look for the character or string of the second argument. If you don't pass the first argument, the function would start from the beginning string of the second argument. If it finds it, it returns the position, a number from where the string2 string was found. If it doesn't find it, it returns 0. 

Character or Sub-String Replacement

When performing some operations on strings, you may want to find out whether a certain character or group of characters has been provided in a string. If so, you may want to replace it with a different character or with a new sub string. To perform this operation, you can call the Replace() function. Its syntax is:

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

The Replace() function will look for the LookFor character or sub-string in the Expression string. If it finds it, it will replace the LookFor character or sub string with the ReplaceWith character or sub-string. These first three arguments are required. Here is an example:

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

In this case, the function would look for s in Elisabeth. If it finds it, then it replaces it with z. If its doesn't find, it would not do anything.

By default, or in the absence of the other arguments, the Replace() function examines the Expression argument from start to end. If you don't want to consider all characters, you can pass the 4th argument to specify from what position of the Expression to work on. Then, the function would consider characters from that position to the end. If you want, you can ask the function to consider only a certain number of characters. To do this, pass a 5th argument as a number.

The Long Text

Introduction

A piece of text is long if it is less than or beyond 255 characteristics. Actually, a field made for long text can hold 1 to 64000 not-formatted characters. To support such long text, Microsoft Access provides a data type named Long Text.

To create a field that can support long simple text:

• In the Datasheet View:
  • Click Click to Add and Long Text from the list that appears

    

  • Click a cell in a column. On the Ribbon, click Fields. In the Add & Delete section, click More Fields and click Long Text

    

• In the Design View, in the top section of the window, set the data type of the field to Long Text

Practical Learning: Introducing the Long Text Type

1. On the Ribbon, click File and click Open
2. In the list of files, click College Park Auto Repair1
3. In the Navigation Pane, double-click the RepairOrders table to open it in Datasheet View
4. On the table, click Click To Add
5. In the menu list that appears, click Long Text
6. Type ProblemDescription and press Enter
7. Click a cell under ProblemDescription and click Fields in the Properties window
8. In the Properties section of the Ribbon, click Name & Caption
9. In the Caption text box, type Problem Description and click OK
10. Click a cell under Click To Add
11. In the Fields tab of the Ribbon, in the Add & Delete section, click More Fields and click Long Text
    
12. Type Recommendations and press Tab
13. Close the table
14. In the Navigation Pane, right-click the RepairOrders form and click Design View
15. Click the Problem Description tab to activate it (you may have to click it twice
16. In the Tools section of the Design tab of the Ribbon, click Add Existing Fields
17. From the Field List, drag ProblemDescription and drop it in the Problem Description tap page
18. Delete its accompanying label

    

19. Click the Recommentations tab page to activate it
20. From the Field List, drag Recommendations and drop it in the Recommendations tab page
21. Delete its accompanying label:

    

22. Save the form and switch it to Form View

The Long Text and SQL

To support long text, the SQL provides two data types named MEMO and LONGTEXT. They are the same as the Long Text of the Ribbon or from the Data Type combo of the Design View of the table. Each of these data types is for a field that should hold up to 65656 characters. Here are examples of using them:

CREATE TABLE Contractors
(
    FullName CHAR(150),
   
    Address MEMO,
    WorkSummary LONGTEXT
);

Characteristics of a Long Text Field

A long text is primarily a text-based object. It shares some characteristics with the short text but it also has some unique behaviors.

After creating a long text field in a table, you can add it to a form or report. Such a text box would be equipped with the necessary characteristics such as an appropriate height and a vertical scroll bar. Of course, you can change them. On the other hand, you can click the Text Box control from the Ribbon, add it to a form or report, and use it for a long text.

Because it is made for long text, a text box for a long text should have an appropriate height. It should also be equipped with one or both scroll bars. One of the features that sets a long text-based text box apart from the short text one is that the user can press Tab in the long text wihout moving to the next control. Also, the user can press Enter in a long text to create a different paragraph.

Practical Learning: Creating String-Based Fields

1. On the Ribbon, click File and click Open
2. In the list of files, click StatesStatistics1 from Lesson 18
3. To start a new table, on the Ribbon, click Create and click Table Design
4. Create the fields as follows:
    

   Field Name	Data Type	Field Size
   Region	Short Text	25
   Description	Long Text

5. Right-click Region and click Primary Key
6. Save the table as Regions and close it
7. In the Navigation Pane, right-click the States table and click Design View
8. Click the first empty cell below Field Name
9. Type Region and press Enter
10. Set the fields sizes of the table as follows:
     

    Field Name	Field Size
    Abbreviation	5
    StateName	40
    Capital	40
    Region	25

11. Save and close the table
12. On the Ribbon, click Database Tools and click the Relationships button
13. In the Show Table dialog box, double-click Regions
14. Double-click States
15. Drag Region from Regions and drop it on Region from States
16. Click Create

    

17. Close the Relationships window
18. When asked whether you want to save, click Yes
19. On the Ribbon, click Create and click Form Design
20. In the Tools section of the Ribbon, click Add Existing Fields
21. In the Field List, double-click Region
22. Double-click Description
23. Double-click the body of the form to access the properties of the Detail section
24. In the Property Sheet, click Format
25. Click Back Color, then click its browse button and select Blue, Accent 5, Darker 50% (Theme Colors: 9th column, 6th row)
26. Design the form as follows:

    

27. Save the form and switch it to Form View
28. Create the following records:
     

    Region	Description
    New England	New England is the group of states in the North-East region. It is delimited in the North and North-East by Canada, in the East by the Atlantic Ocean, and in the South and West by the New York state. New England is one of the regions defined by both the Census bureau and the federal government agencies.
    Mid-Atlantic	Mid-Atlantic is is a region situation in the south of New England. Mid-Atlantic is one of the regions defined by the Census bureau for statistical purposes.
    East North Central	The East North Central region includes the states around the Great Lakes.
    West North Central	The West North Central region includes the states in the Great Planes area. This reqion is divided from the East North Central part by the Mississippi River. This region is characterized by vast agricultural farms and high employment.
    South Atlantic	The South Atlantic region includes the states in the South-East part but also counts the Disctrict of Columbia.
    East South Central	The East South Central portion is one of the regions designated as the South.
    West South Central	The West South Central part is one of the regions with (only) four states. The imposing Texas is both the largest (by itself larger than the other three states combined) and the most populous (its population is higher than those of the other three states combined) state in the region.
    Mountain	Like the name suggests, the Mountain region covers states known for their mountaneous characteristics. They are also covered by desertic areas.
    Pacific	The Pacific region covers the costal western states plus the two non-continental states of Alaska and the Hawaiian islands. All states in this region have a coast on the Pacific Ocean.

29. Close the form
30. In the Navigation Pane, right-click the States form and click Design View
31. In the Controls section of the Ribbon, click the Combo Box and click in the Detail section of the form
32.  In the first page of the wizard, make sure the first radio button is selected and click Next
33. In the second page of the wizard, make sure Table: Regions is selected and click Next
34. In the third page of the wizard, double-click Region and click Next
35. Accept the step and click Next
36. Accept the step and click Next
37. Click the arrow of the combo box and select Region
38. Click Next
39. In the text box, change the label to Region and click Finish
40. Format the combo box and its label to appear like the other controls:

    

41. Save the form
42. Switch the form to Form View and update the records as follows:
     

    Abbreviation	Region
    AL	East South Central
    AK	Pacific
    AZ	Mountain
    AR	West South Central
    CA	Pacific

    

    

43. Close the form
44. In the Navigation Pane, right-click the Summary form and click Design View
45. In the Tools section of the Ribbon, click Add Existing Fields
46. Drag Region and drop it in the Detail section of the Ribbon
47. Move the label to the Form Header section
48. Format both the label and the text box to appear like the controls in the same sections

    

49. Save and close the form

The Rich Text Box

Text is said to be rich if:

• The characters can be formatted to display in different colors
• The words can use different fonts
• The paragraphs can be formatted independently

To create a column or field that can hold rich text, display the table in the Datasheet View:

•  Click Click to Add. In the list that appears, click Rich Text:

  

• Click a cell under the desired column header. On the Ribbon, click Fields. In the Add & Delete section, click More Fields and click Rich Text

Practical Learning: Using Rich Text

1. On the Ribbon, click File and click Open
2. In the list of files, click Altair Realtors1
3. In the Navigation Pane, double-click the Properties table to open in the Datasheet View
4. On the table, click Click to Add
5. In the menu list that appears, click Rich Text
6. Type Description and press Enter
7. Close the table
8. In the Navitation Pane, right-click the Properties form and click Design View
9. In the Tools section of the Ribbon, click Add Existing Fields
10. From the Field List, drag Description and drop it on the form

    

11. Close Microsoft Access
12. If asked whether you want to save, click Yes