Thread: String Functions Listed By Name

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