Microsoft Office Tutorials and References
In Depth Information
Using Custom Functions
Analyzing each character in a string
You can also use a For...Next loop to look at each character in a string.
First, be aware that these two built-in Access functions help with the loop:
Len( string Returns the length of a string in number of characters ):
Mid( string start length Returns a portion of string starting at ):
character start that’s length characters long
As an example, if string is “Hello World”, Len( string ) returns 11,
because there are 11 characters in “Hello World” (counting the blank
space that separates the two words). The expression Mid( string ,7,3)
returns a substring of string that starts at the seventh character and
is three characters in length. In this case, that substring would be Wor,
because W is the seventh character, and the returned substring is three
characters in length.
Text-handling functions are described in more detail in Book III, Chapter 2.
To create a loop that looks at each character in a string one at a time,
start the loop at 1 and end it at Len( string ). Within the loop, use
Mid( string counter ,1) to isolate the single character at the position ,
indicated by Counter. A simple loop that prints each character from the
string named strFull in the Immediate window looks like this:
Sub LookAtEachCharacter()
‘Declare a couple of string variables.
Dim strFull As String, thisChar As String
‘Give strFull a value.
strFull = “Hello World”
‘Now isolate and display each character from strFull.
For Counter = 1 to Len(StrFull)
thisChar = Mid(strFull,Counter,1)
Debug.Print thisChar
Next
End Sub
The For Each...Next loop is a slight variation on the For...Next loop,
and it’s discussed in Chapter 4 of this minibook.
Using Custom Functions
Access has many built-in functions that you can use in expressions. One of
the beauties of VBA is that it allows you to create your own custom
functions, commonly referred to as user-defined functions, or UDFs. After you
create such a function in a standard module, you can use it throughout your
database as you would a built-in function.
Search JabSto ::




Custom Search