Discuss "String Functions Listed By Name" in the Microsoft Access forum.

sbenj69 · #1 April 3rd, 2008, 10:17 PM

This thread is to give a list of the most commonly used string functions in MS Access, the usage and syntax, both in queries and in vba.

Asc - This basically returns the ASCII value of a string. If the string is more than one character long, it uses the first character.
Query usage:
Expr1: Asc("D") - would return 68
Expr1: Asc("Developer Barn") - would return 68 as well
VBA usage:
Code:
```
Dim MyNumber as Integer
MyNumber = Asc("D")
```
Chr - This is the opposite of Asc. This returns the ASCII character based on the number you use.
Query usage:
Expr1: Chr(68) - would return "D"
VBA usage:
Code:
```
Dim MyString as String
MyString = Chr(68)
```
Instr - This stands for in-string. It looks for a string within a string and returns the position. The format for instr is:
Instr(|start position|, "String to search", "Search string", |compare|) start position and compare are optional, compare is rarely used
Query usage:
Expr1: Instr("Developer Barn","Barn") - would return 11
Expr1: Instr(1,"Developer Barn","Barn") - would return 11 as well
Expr1: Instr(10, "Developer Barn", "r") - would return 13
VBA usage:
Code:
```
Dim MyNumber as Integer
MyNumber = Instr(10, "Developer Barn", "r") [/b]
```
Lcase / Ucase - These functions stand for lower case and upper case. They convert all strings to upper/lower case
Query usage:
Expr1: Lcase("DeVeLoPeR BaRn") - would return "developer barn"
Expr1: Ucase("DeVeLoPeR BaRn") - would return "DEVELOPER BARN"
VBA usage:
Code:
```
Dim MyString1 as String
Dim MyString2 as String
MyString1 = lcase("DeVeLoPeR BaRn")
MyString2 = ucase("DeVeLoPeR BaRn")
```
Left / Right - This extracts a certain number of characters from the left or right
Query usage:
Expr1: Left("Developer Barn",9) - would return "Developer"
Expr1: Right("Developer Barn", 4) - would return "Barn"
VBA usage:
Code:
```
Dim MyString1 as String
Dim MyString2 as String
MyString1 = Left("Developer Barn", 9)
MyString2 = Right("Developer Barn", 4)
```
Len - This stands for length, and gives the length of a string
Query usage:
Expr1: Len("Developer") - would return 9
VBA usage:
Code:
```
Dim MyNumber as Integer
MyNumber = Len("Developer Barn")
```
Mid - This function returns a certain number of characters in a string from a certain starting point.
Mid (text, starting position, number of characters)
Query usage:
Expr1: Mid("Developer Barn",11,4) - would return "Barn"
Expr1: Mid("Developer Barn",5,3) - would return "lop"
VBA usage:
Code:
```
Dim MyString as String
MyString = Mid("Developer Barn",11,4)
```
LTrim / RTrim / Trim - These functions remove from a string, the preceeding spaces, following spaces, or both.
Query usage:
Expr1: LTrim(" Developer Barn") - would return "Developer Barn"
Expr1: RTrim("Developer Barn ") - would return "Developer Barn"
Expr1: Trim(" Developer Barn ") - would return "Developer Barn"
VBA usage:
Code:
```
Dim MyString1 as String
Dim MyString2 as String
Dim MyString3 as String

MyString1 = LTrim(" Developer Barn")
MyString2 = RTrim("Developer Barn ")
MyString3 = Trim(" Developer Barn ")
```
Replace - This function replaces part of a string with a replacement string
Replace (Text, Search String, replace with, |start|, |count|) start and count are optional, count determines how many instances to replace
Query usage:
Expr1: Replace("Developer Barn", "Barn", "Expert") - would return "Developer Expert"
Expr1: Replace("Developer Barn", "e", "3") - would return "D3v3lop3r Barn"
Expr1: Replace("Developer Barn", "e", "a", 1, 2) - would return "Davaloper Barn"
Expr1: Replace("Developer Barn", "e", "a", 3, 2) - would return "Devalopar Barn"
VBA usage:
Code:
```
Dim MyString as String
MyString = Replace("Developer Barn", "Barn", "Expert")
```
Space - This function makes a string with a determined amount of spaces, commonly used with concatenation.
Space(Number of Spaces)
Query usage:
Expr1: Space(5) - This returns a string with 5 spaces "_____"
Expr1: "Developer" & Space(1) & "Barn" - would return "Developer Barn"
VBA usage:
Code:
```
Dim MySpaces as String
MySpaces = Space(5)
```
Str - This function converts a field to a string, whether numeric or date
Query usage:
Expr1: Str(1234) - would return a string with the value "1234"
Expr1: Str(#04/03/2008#) - would return a string with the value "04/03/2008"
VBA usage:
Code:
```
Dim MyString as String
MyString = Str(1234567)
```
StrConv - This function returns a string as lower case (1), upper case(2), or proper case(3)
StrConv ( text, conversion type)
Query usage:
Expr1: StrConv("DEVELOPER BARN", 1) - would return "developer barn"
Expr1: StrConv("developer barn", 2) - would return "DEVELOPER BARN"
Expr1: StrConv("dEVELOPER bARN", 3) - would return "Developer Barn"
VBA usage:
Code:
```
Dim MyString as String
MyString = StrConv("developer barn", 3)
```

Coming next week: Date Functions

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/microsoft-access/103-string-functions-listed-name.html
Posted By	For	Type	Date
Query - ASP Free	This thread	Refback	May 30th, 2008 10:15 AM
Help with Truncating a value - ASP Free	This thread	Refback	May 16th, 2008 04:08 PM
String Functions Listed By Name - DeveloperBarn Forums	This thread	Refback	April 5th, 2008 02:22 AM

The Following 2 Users Say Thank You to sbenj69 For This Useful Post:
jmurrayhead (May 27th, 2008), mehere (May 27th, 2008)

Discuss "String Functions Listed By Name" in the Microsoft Access forum.

Microsoft Access - Microsoft Access is a database for small to medium applications. Learn tips and tricks and best database practices here.