MS Access 2010 VBA tutorial- String Manipulation


String Manipulation in VBA

VBA provides a lot of useful functions that can be used to operate strings. The commonly used string functions are UCase, LCase, Trim, RTrim, LTrim, Space, Mid, Val, Left, Right,  and Split.

-Ucase(String): Converts a string to upper case

Example:

Dim s1 As String

s1=”hello”

Dim s2 As String

s2=Ucase(s1)

MsgBox s2

 

-Lcase(String): Converts a string to lower case, in contrast to UCase.

-Trim(String): Removes leading and trailing space from the string.

Example:

Dim s1,s2 As String

s1=” hello ”

s2=Trim(s1)

MsgBox s2


-LTrim(String): Removes leading spaces.


-RTrim(String): Removes trailing spaces.


-Space(number_of_spaces): Adds spaces to a string.

Example:

Dim s1 As String

s1=”hello”

s1=s1 & space(2)

MsgBox s1

 
-Mid(string,start,length): Gets a sub string from the string from the left starting from start argument to the character specified by length.

Example:

Dim s1,s2 As String

s1=”hello”

s2=Mid(s1,1,3)

MsgBox s2


-Val(String_value): Convert a string of value to a value that can be computed.

Example:

Dim val1 As String

Dim val2 As Integer

val1=”12”

val2=Val(val1)

MsgBox “The square=” & val2*val2


-Left(String. length): Get a sub string from the string starting from the left first character to the character specified by length.

Example:

Dim fname,lstname As String

fname=”Yuk Sovandara”

lstname=Left(fname,3)

MsgBox lstname


-Right(String, length): Get a sub string from the string starting from the first right character to the character specified by length.


-Split(String, separator): This function separates sub-strings in a String by a separator character. The result will be an array of sub-strings.

Example:

Dim Names() As String
Dim Stname As String
Stname = "Dara Yuk"
Names = Split(Stname, " ")  'Separating the full name by space character
MsgBox "Last Name: " & Names(0) & " First Name: " & Names(1)

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.