VBA: How to Test if a String starts with a given character




VBA: How to Test if a String starts with certain letters

There are many ways that we can test if the string which begin or start with the certain string or letter. Some time we want to perform the action only on certain field or records that begin or start with the specified letter. For example, we want to know how many customers have first name start with letter “P”. In this How To, I will show introduce the Like comparison operator, InStr, and Left built-in function.

Like comparison operator

We will use Like along with an asterisk (*) to test if the string begins with our substring.

Syntax

“String” Like “SubString*”  

Example of Like

Most of the time, we will use the If Statement to test if the string starts with the specified string. Per example of code below, we will get a message “Yep, this name begins with “Mo”” because the string is “Modesto” and the specified string “Mo*”.  The string “Mo*” means it begins with “Mo” and ignores the rest of the string.

If “Modesto” Like “Mo*” Then

    MsgBox “Yep, this name begins with ” & Chr(34) & “Mo” & Chr(34) & “”

End If

 Other than a hard code of string, it can be refer to the any field on current form or table. Per example below, the message “Yep, this name begins with “Mo”” because “Me.City” is referring to the current City field on current form which has a value as “Modesto.”

Private Sub cmdStartWith_Click()
If Me.City Like "Mo*" Then
    MsgBox "Yep, this city name begins with " & Chr(34) & "Mo" & Chr(34) & ""
   ‘or do something else
End If
End Sub

city-start-with-mo

 

Example of using Like in SQL for Subform

Like built-in function is widely used as a criteria for other functions. For example below, I use Like built-in function to find the category that begins with “t” that I type in the textbox. The result will display on the subform for category name that begin with t letter as shown below.

Private Sub cmdStartWith_Click()
Dim strSQL As String
Dim strLetter, StrCriteria As String
strLetter = Nz(Me.txtBeginWith, 0)
StrCriteria = "[category] like '" & strLetter & "*'"
    strSQL = "select * from category where " & StrCriteria
    Me.Category_DS.Form.RecordSource = strSQL
End Sub

subform-use-like



InStr built-in function

The InStr built-in function can be used to test if a String contains a substring. The InStr will either return the index of the first match, or 0.

Syntax

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

start Is Optional. Numeric expression that sets the starting position for each search.
string1 Is Required. String expression being searched.
string2 Is Required. String expression sought.
compare Is Optional. Specifies the type of string comparison

Example:

IF Statement will be used to test if the return from InStr function is true. The InStr starts at the position 1 on string1 “Hello World” and looks for string2 “Hello W”. If InStr returns 1, it is true that the string1 (“Hello World,”) begins with the string2 (“Hello W”). A message will display “Yep, this string begins with Hello W!” as shown below. More information about InStr at MS website.

If InStr(1, “Hello World”, “Hello W”) = 1 Then

    MsgBox “Yep, this string begins with Hello W!”

End If

Another example, we can use only the String1 and String2, omit the Start and Compare. It is returning True same result as using Like comparison operator above.

If InStr(Me.City, "Mo") =1 Then
      MsgBox "Yep, this city name begins with " & Chr(34) & "Mo" & Chr(34) & ""
      ‘or do something else
End if

city-start-with-mo

Left built-in function

The Left built-in function is the most straight forward way to implement it in VBA.

Syntax

Left(Str, Length)

  • Str – is Required. String expression from which the leftmost characters are returned.
  • Length – is Required. Integer Numeric expression indicating how many characters to return from the left of string. If zero, a zero-length string (“”) is returned.

To determine the number of characters in Str, we will use the Len function. This example demonstrates the use of the Left function to return a substring of a given String. For example below, the given string is “Hello World!” and the number of characters to return from the left of the given string. The characters “He” will return from the Left function of the given string “Hello World!”

Dim TestString As String
TestString = "Hello World!"
  ' Returns "Hello".
  TestString = Left(TestString, 2)
  MsgBox "The 2 letters from the left of TestString = “ & teststring
End If

Create a Function from a Left built-in function

For the convenience of later use, we can create a function that will return True if the string begins with the certain string or character is true. We will use the Left built-in function as a part of function. We will call this function StartsWith() and place  it under the Module so we call it anywhere in this program.

Public Function StartsWith(strText As String, prefix As String) As Boolean   

StartsWith = Left(strText, Len(prefix)) = prefix

End Function

Call Function From Module on current record of form

Private Sub cmdStartWith_Click()
Dim strInv As String
strInv = "209"
   If StartsWith(Me.Customer_Phone, strInv) = True Then
       MsgBox "This customer has a phone number begins with " & Chr(34) & strInv & Chr(34) & ""         
       ‘or do something
   Else
       ‘or do something else
   End If
End Sub

phone-start-with-209



Related posts