How To Use The Expression Builder In access

Summary:

This tutorial is to help you out in knowing what this expression builder feature in access. And mainly how to use this Expression Builder for creating queries in an effortless manner.

We all very well know how much daunting task it is to write an expression. But expression builder this daunting task a lot easier. In an expression there are so many components like functions, constants, operators, identifiers and values are used which get messy to work properly. But by using the MS Access Expression Builder, it’s been so easy to quickly look on these components and insert them accurately.

 Mainly there are two ways to use Expression Builder:

1) using the Expression Builder box

2) or using the expanded Expression Builder, when your expression is in more elaborate form.

Before knowing about what’s this expression builder is or how to use it. Let’s take a quick recap about, what this “Expression” is.

Expression:

Any legal grouping of symbol which results in value is called as an expression. Well the Access database function of Expression builder includes following things, which are very essential in the creation procedure of expression.

  • Operators. For example >=, &, And
  • Built-in functions. For example, Count(), Date(), and MsgBox()
  • Fields. From tables, queries, forms, and reports
  • Constants. Such as True, False, NULL

Where Is The Expression Builder In Access?

You can fetch expression builder from many locations in Access.  but the most convenient one is to display this is by putting focus on property box which takes the expression. Like Control Source or Default Value, and after then click on the Expression Builder  or just simply press CTRL+F2.

Tip:   if word expression is appearing in a menu, just tap on it to begin the Expression Builder.

What’s This Expression Builder In Access?

Expression builder in Access database mainly helps to make expressions for queries, validation rules, default values and much more.

In former version of access database there is no such option of expression builder but the latest Access 2016/2019 include this feature of Expression Builder which eases the process of expressions building.

By making use of the expression builder you can create queries, set validation rule in the field, set a default value on field and more.

It’s a type of lookup wizard, where you search for the list of expressions which is causing trouble. This gives you an option to mix expression into larger, more complex expression which ultimately helps you to make queries and set data rules against field.

How To Use Expression Builder In Access?

In our older posts we have discussed about how to create simple query but in this post, we will create query by using expression builder.

 Step 1: Starting A Query:

  • First of all, we need to start with a new query to make use of the expression builder. After that we can launch expression builder from that particular query.
  • To start the query designer, you need to tap on query design option from create tab present on the access application window ribbon. This will display the Show Table dialog box.

Use Expression Builder In Access 1

  • Now it’s time to make selection of the tables for query. So, select for both of the albums and artists table and add it.

Use Expression Builder In Access 2

  • To close this dialog box, tap to the close
  • Add these fields:
    • ReleaseDate
    • AlbumName
    • Genre
    • ArtistName

Make a check mark against all fields.

Use Expression Builder In Access 3

Steps 2: Launch Expression Builder

After opening of the query, go head and run the Expression Builder.

  • Within the ReleaseDate column, tap into the Criteria cell.
  • Now hit the builder button present on the ribbon. This will open up the expression builder.

Use Expression Builder In Access 4

  • After then we will add a built-in function. For this tap to the function present on left pane, this will further expand its options.
  • Hit the built-in functions and after then on the date/ time present in middle pane.
  • From the right pane, scroll down unless and until you get the Year() Make double-click on Year(), so that it will get displays in the top pane.

Use Expression Builder In Access 5

Remarks:

 This Year() function is  basically used to display the “year” of any specified date given between the brackets.

  • From top pane, hit on the «date»inside the brackets of function so that it gets highlighted.
  • Now it’s time to expand Music.accdb, and after then expand Tables.  Hit on the Albums options to display entire fields of that table. Make double tap on the ReleaseDate to replace «date» present at the top pane.

Use Expression Builder In Access 6

  • Tap on the right section of the function to continue expression building.
  • From the left pane, click on the Operators option and then on the Comparison tab present in middle pane section to see the comparison operators listed there.
  • Make double-click on greater than sign (>) so that it gets added to the top pane.

Use Expression Builder In Access 7

  • Query we have made is actually works for comparing 2 years values, so add another year() function and tap to the «date» in order to highlight it, just we have done before.
  • From the right pane, scroll upward to Date() And make double-click on it for replacing «date».

Use Expression Builder In Access 8

  • Tap on the expression right side, so as to add a less than sign operator (-).
  • From the left pane, tap to the operators and then on the arithmetic in middle pane to display the arithmetic operators.
  • Make a double tap, this will add it to the top pane.

Use Expression Builder In Access 9

On the end section of the expression type 25. This will help to add extra condition to query and so as query will cover that much of years upto which you are mentioned.

For shorter time span, just change the assigned value from 25 to any below year count you want to search.

  • Now the whole process of expression is over. So, tap to the ok option and add this expression into your query. At last close the expression builder.

Use Expression Builder In Access 10

  • Now your query contains the full expression that you have  listed down in Criteriafield. You can freely expand the width of column to see the complete expression.
  • You can now tap to the view or run option for proper execution of the query.

Use Expression Builder In Access 11

Output Of Expression Builder:

Use Expression Builder In Access 12

If you want to learn more about Access database, then you can join this online Access database course:

For Normal Access Course – http://shrsl.com/mts7

For Advanced Access Course – http://shrsl.com/mts8

WRAP UP:

If you are a newbie user of Access database then this post on MS Access expression builder will surely gonna help you a lot. So, try the complete step carefully and if you are rendering any issue mean while this, then ask it freely in our FAQ section.

How To Use The Expression Builder In Access 2010/2013/2016/2019