Add functions to Access expressions

Access has lots of built-in functions you can use to build expressions. If you have read Build an expression, you may recall that functions are just one of the possible ingredients in an expression. However, you can't do too much with expressions without eventually learning how to add functions to them.

Note:  You can also use custom-built functions in your expressions, but if you know how to create those, chances are you don't need an article like this one! You may just be looking for a list of all available Access functions.

First, here's a few basics about using functions:

• To use a function, type its keyword, an open parenthesis, the arguments (values) you want to send in, and then a closing parenthesis.

• Some functions don't need any arguments, but others require several, in which case you separate them with commas.

• Function arguments can be identifiers, constants, or other functions. You can further combine these ingredients using standard mathematical operators (such as +, -, *, or /) or comparison operators (such as <, >, or =).

• Functions take any information you provide as arguments, run some code behind the scenes, and then return a value to the exact place where you typed the function. In other words, you can be a programmer without really doing any programming!

Sound intimidating? It's not really. Maybe an example or two will help:

This expression is made up entirely of the IIf function (the green characters). Note the green commas separating the three arguments. In this example, we use the IIf function to compare a field named DueDate to today's date. If the DueDate field is greater than or equal to today's date, it'll return the string "OK"; otherwise, it'll return "Overdue".

• The first argument of the IIf function is a logical test which consists of the identifier for the DueDate field (in orange), a couple of comparison operators (in red), and the Date function (in blue), which requires no arguments.

• The second and third arguments of the IIf function are both string constants (in black), which must be enclosed in quotes. These two arguments could also be functions or expressions that return numbers or text.

You might use an expression like this in a text box on a form or view, or in a calculated field in a table, among other places.

Here's another example that uses a function in an expression to create a text string:

Here, the expression starts off with a string constant (in black).

• The ampersand (in red) is a concatenation operator, which is used to join strings together.

• The Left function (in green) takes two arguments:

  • A field identifier (in orange), and

  • An integer constant (in black). In this case, we just want the two leftmost characters of the field, so we enter a 2.

If the ProductCode field contained "CD22035", the results of this expression would be "Prefix: CD".

Want more info about expressions and functions?

• For more details about where you enter expressions, see Build an expression.

• Visit MSDN for a list of all available Access functions.