Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,974 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Designing for the end user
- Organizing form elements
- Formatting a form
- Adding headers
- Linking to external content
- Entering and selecting data
- Adding charts
- Creating a main menu
- Creating a customer form
- Understanding report structure
- Building reports from wizards and queries
- Printing reports
Skill Level Intermediate
When skimming a long report, it could be pretty easy to skip right over some very significant numbers. By utilizing the Conditional Formatting rules within your reports, you can make sure that the values that were in the second look get noticed. We will continue building on a Lifetime Sales report that we made in the last movie. I'll double-click on it to open it to review where we are at. Now let's go ahead and switch into Design View. I will change my view here, switch to Design View. We want to call out some data from our report. Let's say that we wanted to highlight all of the values of Lifetime Sales that were less than $20. I will click on Lifetime Sales here, go up to the Format Tab, and click on Conditional Formatting.
This will start up the Conditional Formatting Rules Manager where we can apply rules for formatting our data. I am going to click on New Rule, and we can tell Access that I want to format all the cells where the field value is less than, and I will type-in 20. For all the data that is less than 20 in the Lifetime Sales field, I want to color that red. I will click on this red button here to change the text. We will go ahead and say OK, apply those changes and OK. Let's go ahead and take a look at our report here in Print Preview. I will switch to Design View>Print Preview.
Now, if I scroll down, I will see that the values that are less than $20 have been highlighted red. Now, what if I wanted to change the entire row to red, not just the number, so for instance the name as well? Well we can do that with Conditional Formatting too. I will close Print Preview. I am going to click on the FirstName box and then Shift+Click on LastName. I will return to the Format Tab and click on Conditional Formatting again. I will do New Rule. Now, this time if I were to type in the Field Value is less than 20, Access will be actually trying to compare the name, the FirstName to the value of 20 or the LastName to the value of 20 and obviously that comparison doesn't make any sense.
So what we need to do is actually change this from Field Value Is to Expression, and then we'll put it a calculation that compares the Lifetime Sales value to the value of 20. We can make a reference to Lifetime Sales by wrapping it in square-brackets. So I will write [Lifetime Sales]<20. I will go ahead and format this the same way with the red color and I will say OK, apply the change, and say OK. So now the Conditional Formatting of these two boxes is referencing the value that's in this box here.
Take a look at it in Design View, Print Preview and now we will see that the entire row is red. Now, the expression that you can use with Conditional Formatting can get fairly sophisticated. Let's take a look at another example. It's a little more complicated. Let's say on my report, I wanted to highlight all of my top 100 customers regardless of what state they are in; some states might have none of my top 100 and some states might have four or five people. How can I get a conditional formatting that does that? Now, there's probably lots of different ways that we can go about this. If you know Visual Basic, I am sure there are another few ways you could do it there too. Let me show you one way that we can go about this.
First, I need to identify who my top 100 customers are? I have got a query over here called top 100 customers, and I will double-click on that to run, and we will see that I have 100 records here, I have got the First and LastName and the Lifetime Sales and the State that these people live in. In fact, this is an exact duplicate of the query that we built our entire report off of this TotalSalesCustomers except for one minor change. Let's switch into Design View. This query is exactly the same except I've changed this return value to 100. So it's only returning the first 100 records in that report.
Let's go ahead and close the query. So what I need to do in my Conditional Formatting in the report is make reference to the fields in the top 100 customers query. If the name is on the top 100, then they are going to be here. The way I am going to do that is using a function called DMin. The DMin function looks at a column of data and it finds the minimum value. One more time if I open up this query, and I find the minimum value in this Lifetime Sales field, it's going to be here at the bottom; $135.08. With DMin, I can use this value and use that as a comparison in my Lifetime Sales report.
If I find any values that are at that value or above, then by definition, they're my top 100 customers. So how we would build that query? Let's go ahead and close this Print Preview. I'm going to go back to the Format Tab>Conditional Formatting. I am going to say New Rule, and let me point out real quick here. If I were to actually select all three of these at the same time, and do a New Rule at the same time, since they have slightly different conditions at this moment, they would actually wipe out the first one. So because these first two have the same condition, I could add another one, and it would be just fine, and then I will just copy that to this third one over here, but again if I were to highlight all three at once, it would actually delete the first condition and it puts in the same condition for the other three boxes.
So that's why I have been doing this in two steps. So we'll do a New Rule here. This time I am going to do Expression. I am going to make reference to that same Lifetime Sales field here in the report, then I am going to wrap it in square-brackets. Now I want to say this value if it's greater than or equal to the DMin, I am going to open up a parenthesis for the function, and now I need to make a reference here this top 100 customers field. Now DMin, like all of the other domain aggregate functions like DAverage or DSum has a slightly different syntax than you might be used to.
In order to make reference to those values, we wrap them in quotation marks. So write a quotation mark, and then a square-bracket, the name of the field that I want which is Lifetime Sales in my top 100 customers field, the closing quotation mark, a comma, an open quotation mark, another square-brackets, and then the name of the query I will find that field in; qry_Top100Customers. I will finish it with a closing square-bracket, a closing quotation mark and a closing parenthesis. Now, I can format this how I want.
For these values, I am going to highlight them with this light purple, this Purple 3 color, and just so I have this on my clipboard, I am going to copy this so I can paste it on that other value. So copy, I will press Ctrl+C, so it's stored. Go ahead and say OK, and Apply. Now I will say OK again. Then we will apply that to our Lifetime Sales condition as well; Conditional Formatting>New Rule> Expression Is, and now I will paste that in. Okay. Let's go ahead and see if those works. I am going to say OK, OK, except I need the change of the coloring, so I will go back to Conditional, I will double-click on it, and I will change that background color to Purple 3. All right! That looks good, say OK here.
We will go to the Home Tab, and we will view the Print Preview. It's going to take it a minute to process, but now I have got the purple highlighting for my Top100Customer here, and I have got the red highlighting for everybody that's below $200. It looks like Alaska has a whole bunch of our top 100 club. Now I can clean this up a little bit more. Right now, the purple coloring is just where the textbox is, and if I want to make this look like it's highlighted all the way across the row, I just need to make the textboxes wider. We will close Print Preview, back in Design View and I will just make these textboxes wider.
If they overlap a little bit, that tends to help without any white lines appearing sometimes between them. So I will change my view to Print Preview, and now I have got a purple bar that extends all the way across for my Top100 club customers. So using Expression Builder and a bit of logical problem solving, the Conditional Formatting rules can highlight just about any records that you'd like to call special attention to.