Web.QHMIT.com

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:

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.

  1. Screenshot of the Query Design button highlighted

    Launch the Query Designer

    Click Query Design from the Create tab on the Ribbon.

    The Show Table dialog box will appear.

  2. Screenshot of the Show Tables dialog box

    Select the Tables for the Query

    Select both the Artists and Albums tables and click Add.

    Click Close to close the dialog box.

  3. Screenshot of the Query Designer Screenshot of the Query Designer

    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.

  1. Screenshot of the Query Designer

    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.

  2. Screenshot of 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.

  3. Screenshot of the Expression Builder

    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.

  4. Screenshot of the Expression Builder

    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.

  5. Screenshot of the Expression Builder

    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.

  6. Screenshot of the Expression Builder

    Add another Operator

    Click to the right side of the expression so that you can add a "less than" operator (-).

    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.

  7. Screenshot of the Expression Builder

    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.

  8. Screenshot of the Expression Builder Screenshot of 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.

  9. Screenshot of the Expression Builder

    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.

Screenshot of the query designer

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.

Screenshot of the query in Design View Screenshot of the user prompt

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:

  1. Screenshot of the query in Design View Screenshot of the query in Datasheet View

    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]
  2. Screenshot of the query in Design View Screenshot of the query in Datasheet View

    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]