The Expression Builder helps you build expressions for queries, validation rules, default values, and more.
Access 2016 includes an Expression Builder that simplifies the process of building expressions. You can use the Expression Builder to create queries, set a default value on a field, set a validation rule against a field, and more.
The Expression Builder is kind of like a lookup wizard, where you can browse a list of expressions that you have trouble remembering. It allows you to combine expressions into a larger, more complex expression, that can help you create queries or apply data rules against a field.
An expression is any legal combination of symbols that results in a value. The Expression Builder includes the following to help you build an expression:
Built-in functions. For example, Count(), Date(), and MsgBox().
Constants. Such as True, False, NULL.
Operators. For example >=, &, And
Fields. From tables, queries, forms, and reports.
Previously, we created a simple query. We will now create another query, this time with the help of the Expression Builder.
Using the Expression Builder to help Create a Query
Our database is full of old-school music. Someone has asked if our database contains any albums that were actually released in the last 25 years. So we decide to build a query to find out.
But we want to make it 25 years from the date the query is run. That way, if someone else asks the same question in say, 10 years time, we can re-run the same query and it will look back 25 years from that date.
But we can't quite remember how to construct that query. So... time for the Expression Builder!
First, Start a New Query
Because we're going to use the Expression Builder to help build a query, we need to start a query first. Then we can launch the Expression Builder from within that query.
Now for the Expression Builder
Now that we've got a query open, we can go ahead and launch the Expression Builder.
Same Query, Different Design
There's often more than one way to construct a query in Design View. Often, the same result can be achieved from different query designs.
Here's an example of the above expression, but using a slightly different query construction in Design View.
We could also turn this query into a parameter query. That would make it more useful.
We could get the user to specify how many years back they want the query to go.
All you need to do is replace 25 with [How many years back?] or something similar.
Building an Expression Manually
You don't need to use the Expression Builder if you already know the expression to use. You can simply type it directly into the Criteria field of the query designer.
And even if you do use the Expression Builder, you can still type characters directly into your expression in the top pane. For example, there's no need to navigate all the way to the - symbol if you already know you need it. Just type that part yourself.
Here are two examples of basic expressions that should be easy to remember without needing the Expression Builder: