Access 2016: How to Use The Expression Builder
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.
Launch the Query Designer
Click Query Design from the Create tab on the Ribbon.
The Show Table dialog box will appear.
Select the Tables for the Query
Select both the Artists and Albums tables and click Add.
Click Close to close the dialog box.
Select the Fields to Display
Add the following fields:
- Albums.ReleaseDate
- Albums.AlbumName
- Albums.Genre
- Artists.ArtistName
Keep Show checked against all fields.
Now for the Expression Builder
Now that we've got a query open, we can go ahead and launch the Expression Builder.
Launch the Expression Builder
Under the ReleaseDate column, click inside the Criteria cell.
Now click the Builder button on the Ribbon. This will launch the Expression Builder.
Add a Built-in Function
First, click Functions on the left pane to expand its options. Click Built-in functions and then Date/Time in the middle pane.
On the right pane, scroll down until you see the Year() function. Double-click Year() so that it appears in the top pane.
The Year() function returns the "year" part of whatever date is specified between the brackets.
Add a Field
In the top pane, click «date» inside the function's brackets so that it becomes highlighted.
Now, expand Music.accdb, then expand Tables, then click on Albums to reveal all the fields of that table. Double-click ReleaseDate so that it replaces «date» at the top pane.
Add an Operator
Now click to the right of the function so that you can continue building the expression.
On the left pane, click Operators, then Comparison in the middle pane to reveal the comparison operators.
Double-click the greater than sign (>) so that it's added to the top pane.
Return the Current Year
Our query will be comparing two "Year" values, so add another Year() function, and click the «date» to highlight it, like before.
Now, in the right pane, scroll up to the Date() function. Double-click it so that it replaces «date».
The Date() function returns the current system date.
The Year() function returns the "year" part of that date.
Add another Operator
Click to the right side of the expression so that you can add a minus sign (-).
On the left pane, click Operators, then Arithmetic in the middle pane to reveal the arithmetic operators.
Double-click - so that it's added to the top pane.
Add a Fixed Value
Type 25 at the end of the expression. This how many years we want the query to cover. For a longer or shorter time span, just change it to however many years you need to search.
The expression is now complete. Click OK to add the expression to your query and close the Expression Builder.
Run the Query
Your query now has the full expression listed in the Criteria field. Feel free to expand the width of the column so that you can see the whole expression.
Click View or Run to run the query.
Your Query Results
You can now see that yes, we do in fact have a number of albums that were released within the last 25 years :)
Feel free to save the query as Albums from the last 25 Years or similar.
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.
Parameter Query
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:
After a Certain Date
Query for all albums released after 01/01/1980.
Turn this into a parameter query by replacing >#1/1/1980# with
>[Starting From]Between Two Dates
Query for all albums from the 80s (i.e. released between 1980 and 1990).
Turn this into a parameter query by replacing: >#1/1/1980# And <#1/1/1990#
with
Between [From] And [To]
