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:
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
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
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.
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:
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
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.
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:
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.
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
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 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:
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.
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
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:
Practical Learning: Introducing the Long Text Type
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.
The Rich Text Box
Text is said to be rich if:
To create a column or field that can hold rich text, display the table in the Datasheet View: