Access Text Functions

This list gives a brief description of the most useful functions to manipulate text in the Access VBA language. Many of these functions have an alternate form which returns a string rather than a variant - for example Left() and Left$(). Access Help gives more details and will describe exactly how these functions operate in your version of Access.

Note that VBA functions are always followed by a pair of brackets even if - like Date() - there's nothing in between them. The Access editor will sometimes remove these brackets but by entering them you are telling Access that you are attempting to use a function.

InStr(<string1>, <string2>) Returns the position of <string2> in <string1>.
Join(<array>) Creates a string consisting of the words in <array>. Use Split to create the array. Introduced in Access 2000.
LCase(<string>) Returns <string> in lower case letters. See Ucase.
Left(<string>, <number>) Returns the leftmost <number> characters from <string>.
Len(<string>) Returns the length of <string>.
<string> Like <pattern>) Returns True if <string> matches <pattern>. You can include the wild cards ? and * in <pattern>.
Mid(<string>, <start>, <length>) Returns <length> characters from <string> starting at position <start>. Use the Mid statement to replace characters within a string.
Right(<string>, <number>) Returns the rightmost <number> characters from <string>. Beware of trailing spaces being returned.
Space(<number>) Gives a string of <number> spaces.
Split(<string>) Creates an array holding the words from <string>. Use Join to create a string from the array. Introduced in Access 2000.
StrComp(<string1>, <string2>) Compares <string1> and <string2> and returns -1, 0, or 1 when <string1> is less than, equal to, or greater than <string2>.
String(<number>,<character>) Gives a string of <number> <character>s.
Trim(<string>) Returns the <string> with all leading and trailing spaces removed. Use LTrim() and RTrim() to remove only leading or trailing spaces.
UCase(<string>) Returns <string> in upper case letters. See Lcase.

Other functions

Access Date and time functions

Access logical functions

Access numeric functions