String-Based Data Types
Introduction to Strings
A string is text made of a readable letter, a symbol, a word or a group of words.
When working on a table in the Datasheet View, to create a Text-based field, on the table, click Click to Add and select Text. 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. In the Add & Delete section, click Text .
If a column has been created already and it is using a data type other than 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 Text
If you are creating the table in Design View, to configure a field that can hold any type of string, specify its Data Type as Text:
A Text data type allows the user to type any kind of characters or group of characters. This field can hold up to 255 characters.
Practical Learning: Introducing Strings
|Field Name||Default Value|
The size of a string is usually considered as the number of characters it contains or may hold. If you have just created a Text-based field, whether in the Datasheet View or Design View, its default size is automatically set to 255 characters. This default value is too long in most cases (in some previous versions of Microsoft Access, the default size was 50). In some circumstances, you may need to set a different value. You can therefore decrease or increase this size for any number between 1 and 255:
To change the allowable number of characters of a Text-based field:
When setting the Field Size property by itself, the database would make sure that the user can only type so many characters. For example, it is very unlikely to have a first name of a person that is 50 characters. In this case, you can safely reduce the number of characters to 20 or 30. On the other hand, if you are creating a field whose length is hard to predict, you can set the length to a reasonable value. There are other ways you can decrease the number of characters that are allowed in a field. Over all, you should be able to predict the types of values that would be entered in the field.
Practical Learning: Setting Field Sizes
|Field Name||Field Size|
|1640 Lombardo Ave||Silver Spring||MD|
|10315 Hacht Rd||College Park||MD|
|6366 Lolita Drive||Laurel||MD|
|9002 Palasko Hwy||Falls Church||VA|
The Length of a String
If a control has been created already, when performing an operation on it, you may want to know how many characters it has. To support this operation, Microsoft Access provides the Len() function. Its syntax is:
When calling this function, pass a string in between double-quotes or the name of a control. The function then produces the number of characters in it.
Creating a String in an Expression
A string can be treated as a constant value. As such, it can be passed to a function. However you use a string, if you already know its value, you must include it in double-quotes. Examples are "@", "Q", "Validating", "Cross Over". You can also assign a string to a control. To do this, access the Property Sheet of the control and, in its Control Source, type = followed by the string inside double-quotes.
Because users can be tempted or distracted to enter just anything in a Text-based field, Microsoft Access provides techniques to control what goes in a field and what must be prevented. This is the role of a mask. 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 to display a particular character that the user cannot change. When creating the mask, you will use some predefined characters and create a combination of your choice.
Before creating a mask for a field, Microsoft Access
comes with various masks you can apply to a field to control the user's
input such as dates, times, US Social Security Number, Currency values,
etc. To apply one of these masks, you can use the Input Mask Wizard. To do
this, open the table in Design View. In the top section, click a column.
Set its Data Type to Text. In the lower section of the
table, click the Input Mask property and click its ellipsis button
This would start the wizard.
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, you would click the Edit List button. This would bring 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, you can click the New (Blank) Record button . After creating a new mask, you can click Close.
Once you have located the desired mask, you can click
it and click Next to continue. The second page of the wizard allows you to
accept or customize the mask you had selected. Once you are ready, you can
click Next. The third page presents an option that allows you to specify
how the value in the field would be stored in the database. It presents
two radio buttons and you can either accept the suggestion or select the
other and click Next. The fourth page of the wizard doesn't do much. It
simply lets you know that the wizard is ready to create the mask.
Therefore, you can click Finish.
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:
|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||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|
You can use any of these symbols to create a mask. If you want to include a word or sentence as part of the mask, type it in any section as desired. Here are examples of Custom Masks:
|Mask||Example 1||Example 2||Example 3|
|\(000") "000\-0000||(301) 294-6464|
|!\(999") "000\-0000""||( ) 392-3873|
Practical Learning: Using Input Masks
|1640 Lombardo Ave||Silver Spring||MD||20904-1136|
|10315 Hacht Rd||College Park||MD||20747|
|6366 Lolita Drive||Laurel||MD||20707-1014|
|9002 Palasko Hwy||Tysons Corner||VA|
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. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general syntax of the concatenation operator 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 field. Just as you can involve two values in a concatenation, you can also use more than one. All of them be can constant strings. The operation can involve only the names of fields, or the expression can be a combination of constant values and names of fields.
To concatenate more than two strings, you can use as many & operators between any two 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 use 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, you can use the LTrim() function. 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, you can use the RTrim() function. Its syntax is:
RTrim(Expression As String) As String
The string to trim is passed as argument to the function. If that string has space on its right, this function would remove it and return the same string without any trailing space. Otherwise, the original string would be returned.
To remove empty spaces on both the left and the right sides of a string, call the Trim() function. 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
There are various techniques you can use to convert a
string from lowercase to uppercase and vice-versa. An alphabetical
character is recognized as being in lowercase if it is one of the
following characters: a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q,
r, s, t, u, v, w, x, y, z. On the other hand, a character qualifies as
uppercase if it is one of A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P,
Q, R, S, T, U, V, W, X, Y, Z. All the other symbols are ignored even if on
the keyboard you would press Shift to type them.
To convert a lowercase character or string to uppercase, you can use the UCase() function. Its syntax is:
UCase(Expression As String) As String
This function takes a string as argument and examines each one of its characters. If a character is an alphabetic character in lowercase, it would be converted to uppercase. If the character is either an alphabetical character in uppercase or it is not an alphabetic character, it would be kept "as is".
To convert a character or a string from uppercase to lowercase, you can call the LCase() function. Its syntax is:
LCase(Expression As String) As String
This function takes a string as argument and examines each one of its characters. If a character is an alphabetic character in uppercase, it would be converted to lowercase. The case of all the other characters would be ignored.
String comparison allows you to find out which one of two strings is longer or whether both strings are equal. When comparing two strings, the database engine checks lowercase and uppercase characters. To compare two strings, you can use the StrComp() function. 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.
There are many operations you can perform on individual characters of a string. These include creating a sub-string, checking for a character, finding the position of a character, or deleting a character or characters. These operations can be valuable when creating objects such as login dialog boxes.
To create a string that is made of one or more characters from the left side of a string, you can 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. The function would then create a new string that is made of the number of characters from the left side of the first argument. To illustrate, consider a string such Republic passed as the first argument and consider that you pass 3 as the second argument:
The function would consider the left 3 characters of the Republic argument and it would produce that sub-string. The resulting string would be Rep.
To create a string that is made of one or more characters from the right side of a string, you can call the Right() function. Its syntax is:
Right(Expression As String, Length As Number) As String
This function also takes a string and a number as arguments. The function then creates a new string that is made of the number of characters from the left side of the first argument. Consider the following example:
The function would consider the last 5 characters of the Midnight argument and it would produce that sub-string. The resulting string would be night.
To create a string that is made of one or more characters from anywhere in a string, you can call the Mid() function. Its syntax is:
Mid(Expression As String, start As Number, Optional Length) As String
This function also takes 3 arguments with required: a string, a starting position and an optional number. The function creates a new string using the first argument, from the start number. If you don't pass the third argument, the function returns a string from that position to the end of the string. Consider the following example:
This function would start creating a new function from the second position of Ideal to the end. The resulting string would be "deal".
If you pass the third argument, the function would create a new string from the start position. Then it would count the Length number of characters from the start position and it would create that new string. Consider the following example:
Mid("Elizabeth", 2, 4)
This would produce "liza".
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 substring and use that information as you see fit. To support this operation, you can use the InStr() function. 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, then 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.
String-Based Controls: The Memo
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:
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.
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.
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
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:
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 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.
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.
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:
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
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:
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:
Practical Learning: Attaching Objects to a Record
Yugo National Bank
|Field Name||Data Type||Field Size||Caption|
|AccountTypeID||AutoNumber||Account Type ID|
|Field Name||Data Type||Field Size||Caption|
|TransactionTypeID||AutoNumber||Long Integer||Transaction Type ID|
|Field Name||Data Type||Field Size||Caption|
Watts A loan
|Field Name||Data Type||Field Size||Caption|
|Field Name||Data Type||Field Size||Caption|
|Field Name||Data Type||Field Size||Caption|