Explore the Expression Builder interface

- [Instructor] 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 to help us construct our expressions. Let's go ahead and take a look at the Customer-LookupQuery, that we created in the last chapter, and we go ahead and open it straight into Design View. Now the tool that I want to take a look at is called, The Expression Builder, and you can activate it in a number of different ways. First, we need to find where we want the expression to occur. You can invoke the Expression Builder in lots of different places inside of Access. For instance, if I right-click in the blank field over here to the right of the StateName Column. You'll see that I can get to the Builder this way. I can also use the Expression Builder in Criteria's. For instance, if I click in the Criteria Row, you can see I can get into it there. There' another place that you can get to the Expression Builder, and that's up here on the Ribbon. You'll see you have the Builder Icon there, with the Builder Button. So, what is the Expression Builder for? Well, let's go ahead and use the Expression Builder to recreate the criteria that we established, in the last chapter, that linked to the form here called, Customer-LookupForm, specifically a Combo Box on that form, called, cboStateLookup. I'm going to highlight this here in the StateName Criteria section and just press Backspace on my keyboard to get rid of it. Then, I'm going to right-click in that cell, and go to Build. And that will launch the Expression Builder. Now, like I said, you can get to the Expression Builder in lots of different places, inside of Access, but it always works the exact same way. Up at the top, we have this section here called, The Expression Area, and this is where we are going to type in our formula or our function. Down below, we have three different windows and these will expand as we start to make selections. The first one is the Expression Elements, and we have several different folders here. The first one here is called Functions, and if I open that one up, you'll see we have some built-in functions. I'll have some functions that are associated with this database, if we've created an custom ones, inside of Visual Basic, for instance. And then I also have access to some web services. If you go into the Built-In Functions folder, the expression categories populates. Now if you've ever used Excel, you might be familiar with the Function Library, and this is essentially the same thing here inside of Access. We have lots of different categories of different kinds of functions. For instance, we have Array Functions or Conversion Functions. We have date and time functions, and so on. As you click through these different categories you'll see some different expression values appear over here on the right. And if you click on any of these, you'll some details down below about what that particular function does. For instance, the Date Function that I have selected now, returns a variant of type Date containing the current system date. Basically, this is just an easy way to get the current date of your computer. So those are some of the Built-in Functions, let's go back here and collapse the Functions Folder, and take a look at the one down below. Right now it says, H+ Sport - Expression Builder, this is the name of the database that I'm currently working with. If I expand that open and you will see that we have collections of different tables, queries, forms, and reports. These are all the different objects inside of my database. If I expand the Tables Collection, we'll see all the different tables we have. I can collapse that, and take a look at all the Queries that we have in the database, as well as all the Forms or the Reports. Now inside the Forms and Reports sections we actually have two groups. One for Loaded Forms, and this would populate with any Forms that we had currently open. I don't have any Forms open in my database right now, so I'm not seeing anything here. But if I click on All Forms, and expand that open, we'll see the three different Forms that we have over here in the Navigation Pane down at the bottom. So let's go ahead and repopulate the Criteria back here in our original Query. And if you remember, that criteria was coming out of a form called, Customer-LookupForm, which is the first one here on the list. When I select it, the Expression Categories expands to show me all the different objects on that form. Remember earlier that I said that all objects, inside of your Access databases, have names and values that we can make use of. That even applies to things like the image that we are using in the background on that form, or the logo that we're using. Now not all of these are going to be useful but one that will be useful is the cboStateLookup. This is the name of that combo box that we're pulling the StateLookup information from. If I double-click on that, that'll populate up here into the Expression section, and you can see it has the same syntax that we previously typed in manually. It says to go into the Forms group, find a form called, Customer-LookupForm, and on that form you'll find a section called, cboStateLookup. Whatever the value of this combo box is will get populated into the criteria in our underlying query. Let's go ahead and say okay to that, and that gets repopulated down here into the criteria section, just like we typed it in manually. Now I can go ahead and save the query, and if I run it, I'm actually going to get an enter parameter value request, and that's because the form isn't currently open. Let's go ahead and cancel out of that. I'll go ahead and close the query. And now we can go back to the Customer-Lookup Form just to make sure that everything still works. I'll use the drop-down menu to select a state, and I'll press the Run Query Button to open up that query. I get two results for the state of Delaware, so it looks like everything is still working. So, here we use the Expression Builder to help create a criteria, rather than having to type it in all manually. As you get more experienced writing out the expression that you want to use, you may 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.