Join Adam Wilbert for an in-depth discussion in this video Exploring the Expression Builder interface, part of Access 2013: Queries.
Writing out various functions and expressions can quickly get complicated, especially when you're making use of multiple tables and queries across your database. Luckily, Access provides a tool that will help us construct our expressions. Let's go ahead and edit the Customer Lookup Query that we created in the previous chapter. I'm going to right-click on it, and jump straight into design view. Now, this particular query uses a criteria here that refers to a form. Let me right-click on it and open up a Zoom box. We can see that it refers to the form called Customer Lookup Form, and within that form, we're looking for a combo box called CBO State Lookup.
Now previously, we had entered in this text manually. Let's take a look at another way that we could've done that. Let's go ahead and say OK here, and then I'm actually going to press the Delete key to remove that from the criteria. Once again I'm going to go ahead and right-click here, and this time instead of choosing the Zoom box, I'm going to choose this option here called Build. That'll open up what's called the Expression Builder. The Expression Builder will help us construct expressions that we can use with criteria and fields in our queries. It's separated into four main pieces. The upper window right up here is where the text will appear that'll get copied into the criteria when we're finished.
This first window here on the left, includes Expression Elements and we can drill into various areas of this to pull out different elements. For instance, if I open up this plus button here next to Functions, we'll see a listing of some built-in functions, and functions that are inside of our database. If I click on Built-in Functions, then the second panel opens up. It's called Expression Categories. Here we have different categories of functions that we can pull from. For instance, Arrays functions or Conversion functions or Date and Time functions. Once we've selected a category, we have various expression values over here.
These are the actual names of the functions that we can apply to our data. For instance, within the Date and Tme category, I have a function called Date, or I have a function called Day, or Hour and so on. Now if I go back to this first panel here and collapse my functions, we'll see we also have a folder that represents our database itself. Right now I'm in the KinetEco_04_01 database. If I expand this open, we'll see all of the different elements that are inside of this database. We have our tables, our queries, our forms, and our reports. If I open up my Forms, for instance, I get two subcategories.
Loaded forms are any forms that I might have open right now. I don't have any open, so if I click on it, I don't see anything in the second window. All Forms are going to be all of the forms that my database. Right now I have these two forms in my navigation pane, so if I expand this open, we'll see both of those forms here, Department Phone List and Customer Lookup Form. I'm going to go ahead and choose the Customer Lookup Form here. When I choose the form, all the various elements that are on that form appear in this second window. For instance, I can see the button here, this is this Command2, is the name of the button.
I have a label on that form. I have the detail section of the form. And I also have a reference to our combo box, the CBO State Lookup. If I double-click on this, it adds in the text up above. This is exactly what we had typed in previously. In the forms group, we'll find a form called Customer Lookup Form, and on that form, we'll find a combo box called CBO State Lookup. So, rather than manually typing this in, we could've used the Expression Builder. Just go into the database object, into Forms, into All Forms, into the specific form you're looking for, and then double-click on that object to add it up above.
Let's go ahead and say OK, and that'll drop that down into the criteria row again down here. Now if we run our query, it's going to give us a parameter value request because that form isn’t currently open. So, let me press the Escape key and open up the form. Now if I choose a value from the form and press Run Query, it runs just like it did before. So, as you get more experience, writing out the expressions that you want to use, you might find it quicker to just write them out by hand directly in the design grid. But, for learning the proper syntax, troubleshooting, or for exploring and discovering how new functions work, the Expression Builder is a fantastic tool.
- Defining criteria
- Understanding comparison operators
- Using joins
- Creating parameter queries
- Using Expression Builder to work with functions
- Working with dates and times
- Creating conditional statements
- Finding duplicate records
- Creating backups
- Making, deleting, and appending records
- Understanding SQL basics and writing SQL queries
- Useful query tricks