Access - String Functions in Queries, Forms and Reports

Access Topic - String Functions in Queries, Forms and Reports

http://campus.houghton.edu/orgs/adminsys/Access/Qry_String_Functions.htm

When creating queries, you sometimes need to manipulate the contents of fields. For instance, you may wish to extract the first and last names from a Full Name field into separate fields in the query. This type of action can be performed using string functions which are built into Access.

Below is a list and brief description of the string functions available for use in queries, forms and reports. The examples below use quoted strings in the examples so you can see what we're working with. You will be using field names enclosed in square brackets (i.e. - [AA_NAME]) in place of string in the function.

More information about these functions can usually be found by entering the function name in the Help Wizard in Access.

Function	Description	Example	What you get
Between num1 And num2	Returns records whose numeric or date field falls between num1 and num2.	Between "02/01/02" And "02/28/02"	Records with this date field in the month of February.
Chr(number)	Returns the character corresponding to the numeric ANSI (ASCII) value number.	Chr(13)	Carriage Return
Format(string,formatting)	Formats an expression string according to the formatting string entered.	Format(Date, "dd-mmm-yy)	15-Jul-03
IIf(condition,then,else)	Allows IF logic to be performed in a field expression. In a query, you would use "IIf" in the field row to modify the contents of a new field like "Foreign" based on some criteria condition. The example sets the value in the field called "Foreign" based on the "Country" field.	Foreign: IIf([Country]<>"US", "Yes","No")	"Yes" for countries other than US, "No" for US.
Instr(string,search.char)	Returns the position of search.char within string.	Instr("ABCD","C")	3
LCase(string)	Returns the lowercase version of a string.	LCase("ABCD")	abcd
Left(string,num.of.chars)	Returns the leftmost num.of.chars of string.	Left("ABCDEF",3)	ABC
Len(string)	Returns the number of characters in string.	Len("ABCDE")	5
LTrim(string)	Removes leading spaces from string.	LTrim(" ABC")	ABC
Like "string*"	Returns records with the first part of the field matching string.	Like "ABC*"	Any record with the beginning of this field equal to "ABC"
Mid(string,start,length)	Returns a portion of string from the start character (a number) for length characters long.	Mid("ABCDE",2,3)	BCD
Right(string,num.of.chars)	Returns the rightmost num.of.chars of string.	Right("ABCDEF,3)	DEF
RTrim(String)	Removes trailing spaces from string.	RTrim("ABC ")	ABC
Space(number)	Returns a string consisting of a specified number of spaces.	Space(5)	" " (without the quotes)
Str(number)	Converts number to a text string.	Str(123.45)	"123.45" (without the quotes)
String(number,character)	Returns a string consisting of the specified repeated character for number of times.	String(5,"A")	AAAAA
Trim(string)	Removes leading and trailing spaces from string.	Trim(" ABC ")	ABC
UCase(string)	Returns the uppercase version of string.	Ucase("abc")	ABC