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

Access Tips

FoxPro Tips

General Tips

 

Related Items

Alvechurch Data - Microsoft FoxPro and Access Database Development and Training

Alvechurch Data are based close to Birmingham and provide training and development support for small businesses using Microsoft FoxPro, Visual FoxPro and Access databases.

Read More

Custom Toolbars

Your Access database will look more impressive if you add custom toolbars.

Read More

Development Services

Alvechurch Data specialise in Microsoft Access and FoxPro and have fifteen years experience developing databases for small business use.

Read More

Training Services

Details of the training courses in Microsoft Access and FoxPro offered by Alvechurch Data.

Read More

Support Services

How Alvechurch Data can help you with maintenance and support of your Access and FoxPro databases.

Read More