In this movie, Adam introduces the Expression Builder. The Expression Builder is a tool that assists in the creation of functions and formulas and will populate them back into the query design grid when complete. It acts as a repository for discovering and learning about all of the built-in functions that ship with Access.
- View Offline
- [Voiceover] 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 us a tool that will help us construct our expressions. Let's take a look at an example built inside of our Customer Lookup Query that we made previously. We're gonna right-click on it, and go into Design View. Now, when we first made this query, we handcrafted this criteria by typing into the Zoom Box. And you can see it's quite long, if I expand this open, you can see the whole thing. Now instead of hand-typing this, what we can do is use something called the Expression Builder.
I'm gonna highlight all of this and just press Delete on my keyboard to get rid of it. We can launch the Expression Builder in a couple of ways. Up here on the Design tab of the ribbon, we can find a button here called Builder, and it's got this little magic wand icon with the three dots below it. The other way to launch the Expression Builder is probably the easier way, and the way I tend to prefer. I'm just gonna right-click in this criteria box underneath State Name. And from the pop-up menu, you'll see an option here called Build with the same icon. Go ahead and select that and that'll launch up the Expression Builder. The Expression Builder window is split into four sections.
We've got this area up here on the top, which is just a large blank area where we can use to type in our expressions. You could think of this kind of like that Zoom Box where we have lots of space to type. Down below, we have three different panels, and these will expand as we make selections here in the very first one. This first one is called the Expression Elements, and we have lots of different categories of things that we can find inside of here. For instance, we have a section here called Functions, and if I expand this open, we have a couple of options. We have the Built-in functions built into the Access program itself. We also have a section for functions that might be written or custom-written inside of our database.
Or we have some Web services we can choose from. Now if you've ever used Excel, you might be familiar with the function catalog books that are available inside of that program. You could think of these Expression categories as the same thing. We've got different categories of functions, for instance, we have subversion functions, we have date/time functions, or we have some general math functions, or aggregate functions or text functions. And inside of each of these categories, we have lots of different functions that we can apply to our data. For instance, inside the text function category, we have a function here called Format. If I double-click on that, that'll add in the function up here, and we can just fill in the different parts.
Let's go ahead and highlight all of this and delete it out. In addition to the different functions that are available, we can go into our actual database itself. Back here in the very first panel, the H+ Sport Expression Builder section, which is the name of the current database that I'm working in, also has a plus next to it. If I click on that plus, we'll see different categories for Tables, Queries, Forms, and Reports. And if I expand each of these, you'll see that these are the tables that live over here in the Navigation pane. So I can see all of the tables in this database, I can see all of the queries in this database, and I can see all the different forms and reports.
Now what this allows us to do is to go through the different objects in our database and make selections of the various elements that are on those forms and reports. For instance, if I go into the Forms folder, I can find two sections. One's called Loaded Forms, and if I select that, that'll show me all the different forms that may be open at the moment. I don't have any forms that are open right now, so I'm not seeing anything. But if I go into All Forms, we'll see all the different forms that are currently in my database. And right now I've got three. I've got this Customer Lookup form, Department E-mail list, and this H+ Sport Blank which would appear down here if I could scroll down.
If I select this first one, the Customer Lookup form here, that's where we can find all of the different things that are on that form. Including the image that's in the background, we have the Command button, which is the button when we run to... Or press to run the query. We've got a Label here, and we have this CBO State Lookup, which is that combo box that we created earlier. If I double-click on that, that's gonna add that exact same syntax that we previously hand-wrote in the Zoom Box. So you can see we've got the Forms group, inside of that we've got a form called Customer Lookup form, and inside of that we've got a combo box called CBO State Lookup.
If that's all I need to do, I can just double-click on that, add that syntax up here, and when I press Okay, you'll notice that that text gets added down here right into the criteria right where started a moment ago. And just to verify that everything still works, let's go ahead and close this query. We'll save the changes here. And we'll go down and find the Customer Lookup form, and we can use the drop-down list to choose a state, run the query, and it works just like it did before. So here we used the Expression Builder to help create a criteria, rather than having to type it all manually. As you get more experience writing out the expressions that you wanna use, you might find it quicker to just write them out by hand, directly in the Design grid like we did earlier.
But, for learning the proper syntax, troubleshooting, or for just exploring and discovering how functions work, the Expression Builder is a fantastic tool.
- Creating a query with the wizard
- Defining query criteria
- Using comparison and wildcards in criteria
- Working with joins
- Creating parameter queries
- Using the built-in functions in Access
- Summarizing data
- Aggregating records with totals
- Working with dates
- Creating alternative queries: unmatched, crosstab, and more
- Writing queries with SQL