Microsoft Access Database Development With VBA

Strings

 

Strings and Code

 

A String Variable

A string variable can be declared using the String data type. After declaring a String variable, it is initialized as empty. You can also initialize it with any string of your choice. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = "République d'Afrique du Sud"
    MsgBox strValue
End Sub

This would produce:

String

The Length of a String

In many operations, you will want to know the number of characters a string consists of. To get the size of a string, you can call the Len() function. Its syntax is:

Len(strValue As String) As Integer

The Len() function takes one argument, which is the string you are considering. The function returns the number of characters of the string. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    Dim iLen As Integer
    
    strValue = "République d'Afrique du Sud"
    iLen = Len(strValue)
    MsgBox "The length of """ & strValue & """ Is " & _
            CStr(iLen) & " characters."
End Sub

This would produce:

String

The String() Function

One way you can initialize a string is to fill it up with a certain character of your choice. To do this, you can call the String() function. Its syntax is:

String(number, character) As String

The second argument to this function is a character you want to repeat in the string. The first argument to this function is the number of occurrences you want the second argument to be repeated in the string. The String() function returns a String value. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = String(18, "#")
    MsgBox strValue
End Sub

This would produce:

String

The String Object

 

Strings and Empty Spaces

The simplest string is probably one that you declared and initialized. In some other cases, you may work with a string that you must first examine. For example, for some reason, a string may contain an empty space to its left or to its right. If you simply start performing a certain operation on it, the operation may fail. One of the first actions you can take on a string would consist of deleting the empty space(s), if any on its sides.

To remove all empty spaces from the left side of a string, you can call the LTrim() function. Its syntax is:

LTrim(string) As String

To remove all empty spaces from the right side of a string, you can call the RTrim() function. Its syntax is:

RTrim(string) As String

To remove the empty spaces from both sides of a string, you can call the Trim() function. Its syntax is:

Trim(string) As String

On the other hand, if you want to include or add empty spaces in a string, you can call the Space() function. Its syntax is:

Space(number) As String

Because all these functions return a string, they can be written as LTrim$, RTrim$, Trim$, and Space$.

Strings Comparisons

To compare two strings, you can call the StrCmp() function. Its syntax is:

StrComp(string1, string2, compare)

The first and the second arguments to this function are strings and both are required. After the function has performed the comparison, it returns

  • -1 if string1 is less than string2
  •   0 if string1 and string2 are equal
  •   1 if string1 is greater than string2

The third argument allows you to specify the comparison in binary or text format. This argument can have one of the following values:

Constant Value Description
vbBinaryCompare 0 Perform a binary comparison
vbTextCompare 1 Perform a textual comparison

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue1 As String
    Dim strValue2 As String
    Dim iComparisonValue
    
    strValue1 = "République d'Afrique du Sud"
    strValue2 = "Republic of South Africa"
    iComparisonValue = StrComp(strValue1, strValue2, vbTextCompare)
    
    MsgBox "Comparing """ & strValue1 & """ with """ & _
           strValue2 & """ produces " & CStr(iComparisonValue)
End Sub

This would produce:

String

Reversing a String

Once a string has been initialized, one of the operations you can perform on it consists of reversing it. To do this, you can call the StrReverse() function. Its syntax is:

StrReverse(string) As String

This function takes as argument the string that needs to be reversed. After performing its operation, the function returns a new string made of characters in reverse order.  Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strRev As String
    
    strValue = "République d'Afrique du Sud"
    strRev = StrReverse(strValue)
    
    MsgBox strValue
    MsgBox strRev
End Sub

This would produce:

String

String

Because the StrReverse() function returns a string, you can write it as StrReverse$.

 
 
 

The Character Cases of a String

 

Introduction

Each alphabetical character in the English language has two representations: lowercase and uppercase. Characters in lowercase are: 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, and z. Their equivalent characters in uppercase are represented as 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, and Z. Characters used for counting are called numeric characters; each one of them is called a digit. They are 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. There are other characters used to represent things in computer applications, mathematics, and others. Some of these characters, also called symbols are ~ , ! @ # $ % ^ & * ( ) _ + { } ` | = [ ] \ : " ; ' < > ? , . / These characters are used for various reasons and under different circumstances. For example, some of them are used as operators in Mathematics or in computer programming. Regardless of whether a character is easily identifiable or not, all these symbols are character types.

Converting a String to Uppercase

To convert a lowercase character to uppercase, you can use the UCase() function. Its syntax is:

UCase(strValue As String) As String

This function simply takes one argument, the string that needs to be converted. Each letter of the string that is already in uppercase would remain in uppercase. Each letter of the string that is in lowercase would be converted to uppercase. Each non-letter character of the argument would not be converted. This function returns a string value. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = "République d'Afrique du Sud"
    MsgBox strValue
    strValue = UCase(strValue)
    MsgBox strValue
End Sub

This would produce:

String

String1

Because the UCase() function returns a string, you can also write it as UCase$.

Converting a String to Lowercase

To convert a string to lowercase, you can call the LCase() function. Its syntax is:

LCase(strValue As String) As String

This takes one argument as the string to convert. Any letter in lowercase in the argument would remain in lowercase. Any letter in uppercase would be converted to lowercase. The non-letter characters of the argument would be kept “as-is”.

Since the LCase() function produces a string, you can also write it as LCase$.

Conversion to Appropriate Case

The LCase$() and the UCase$() functions are used to process the whole string. An alternative to these two functions consists of calling the StrConv() function. Its syntax is:

StrConv(string, conversion As vbStrConv, LocalID As Long) As String

This function takes two required arguments. The first argument is the string that will be considered for conversion. The second argument allows you to specify how the conversion would be performed. This argument can have one of the following values:

Constant Value Description
vbUpperCase 1 Converts the lowercase letters of the string to uppercase
vbLowerCase 2 Converts the uppercase letters of the string to lowercase
vbProperCase 3 Converts the first letter of every word of the string to uppercase
vbWide 4 Converts narrow (single-byte) characters in string to wide (double-byte) characters
vbNarrow 8 Converts wide (double-byte) characters in string to narrow (single-byte) characters
vbKatakana 16 For Japan, converts Hiragana characters in string to Katakana characters
vbHiragana 32 For Japan, converts Katakana characters in string to Hiragana characters. 
vbUnicode 64 Converts the string to Unicode using the default code page of the system.
vbFromUnicode 128 Converts the string from Unicode to the default code page of the system

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strConversion As String
    
    strValue = "republic of south africa"
    strConversion = StrConv(strValue, vbProperCase)
    
    MsgBox strValue
    MsgBox strConversion
End Sub

This would produce:

String

String

Sub-Strings

 

Introduction

A sub-string is a string that is created or retrieved from an existing string. Once again, the Microsoft Visual Basic library provides different techniques of creating it. These include getting characters from the left, from the right, or from any part inside the string.

Creating a Sub-String

The Left() function can be used to get a number of characters from the left side of a string. The syntax of this function is:

Left(string, length) As String

This function takes two required arguments. The first argument is the string on which the operation will be performed. The second argument, a constant integer, is the number of characters to retrieve from the first argument. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strLeft As String
    
    strValue = "République d'Afrique du Sud"
    strLeft = Left(strValue, 10)
    
    MsgBox strValue
    MsgBox strLeft
End Sub

This would produce:

String

String

Because the Left() function produces a string, you can also write it as Left$ with the $ sign indicating that it returns a string. As its name suggests, the Left() function creates a new string using characters on the left side of the considered string. If you want your sub-string to contain characters from the right side of a string, you can call the Right() function. Its syntax is:

Right(string, length) As String

This function follows the same rules as the Left() function except that it works from the right side. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strRight As String
    
    strValue = "République d'Afrique du Sud"
    strRight = Right(strValue, 14)
    
    MsgBox strValue
    MsgBox strRight
End Sub

This would produce:

String

String

As mentioned for the Left() function, you can write the Right() function as Right$ to indicate that it returns a string.

While the Left$() and the Right$() functions work on both sides of a string, you may want to use characters starting at any position of your choice to create a sub-string. This operation is supported by the Mid() function. Its syntax is:

Mid(string, start[, length) As String

This function takes two required and one optional arguments. The first argument, which is  required, is the string on which the operation will be carried. The second argument, also required, is the position from where to start the sub-string inside the string argument. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strMid As String
    
    strValue = "République d'Afrique du Sud"
    strMid = Mid(strValue, 14)
    
    MsgBox strValue
    MsgBox strMid
End Sub

This would produce:

String

String

As you can see, if you omit the third argument, the returning sub-string would start at the start position from the string argument up to the end of the string. If you prefer, you can create a sub-string that stops before the end. To do this, you can pass the number of characters as the third argument. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strMid As String
    
    strValue = "République d'Afrique du Sud"
    strMid = Mid(strValue, 14, 7)
    
    MsgBox strValue
    MsgBox strMid
End Sub

This would produce:

String

String

Finding a Character or a Sub-String in a String

If you have a string and want to find a character or a sub-string in it, you can call the InStr() function. Its syntax is:

InStr([start, ]string1, string2[, compare])

The first argument specifies the position from the string where to start looking for. This first argument is not required. The second argument is required and specifies the string to examine. The third argument specifies the character or the string to look for in the second argument. The fourth argument, which is optional, specifies whether the criterion would be binary or text-based.

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim iPos As Integer
    
    strValue = "Republic of South Africa"
    iPos = InStr(1, strValue, "South")
    
    MsgBox "In """ & strValue & """, " & " South can be found at " & CStr(iPos)
End Sub

This would produce:

String

The InStr() function works from the left side of the considered string. If you want to find an occurrence of one or more characters from the right side side of a string, you can use the InStrRev() function instead.

Replacing Occurrences in a String

After finding a character or a sub-string in an existing string, one of the operations you can perform would consist of replacing that character or that sub-string with another character or a sub-string. To do this, you can call the Replace() function. Its syntax is:

Replace(expression, find, replace[, start[, count[, compare]]])

The first argument to this function, expression, is required. It holds the string that will be considered.

The second argument, find, also required, is the character or the sub-string to look for in the expression.

The third argument also is required. It also is passed as a string. If the find string is found in expression, it would be replaced with the replace string.

When you call the Replace() function with the three required arguments, it would proceed from the most left character of the expression string. Instead of start with the first character, you can specify another starting position of your choice within expression. The fourth argument, which is optional, allows you to pass this factor as a constant integer.

The fifth argument also is optional. If you omit it, every time find would be found in expression, it would be replaced with replace, as many times as possible. If you want, you can limit the number of times this replacement should occur even if find is found more than once. To specify this factor, pass the fifth argument as a constant integer.

The compare argument, also optional, allows you specify whether the comparison would be carried in text or binary format. This argument can be passed as Text or Binary.

 
 
   
 

Previous Copyright © 2002-2015, FunctionX, Inc. Next