Start learning with our library of video tutorials taught by experts. Get started

Access 2010: Forms and Reports in Depth

Formatting conditionally


From:

Access 2010: Forms and Reports in Depth

with Adam Wilbert

Video: Formatting conditionally

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.
Expand all | Collapse all
  1. 1m 27s
    1. Welcome
      1m 2s
    2. Using the exercise files
      25s
  2. 15m 53s
    1. Introducing forms
      2m 41s
    2. Designing for the end user
      45s
    3. Exploring the database
      1m 49s
    4. Creating a form with the Form Wizard
      6m 43s
    5. Refining the form in Layout view
      3m 55s
  3. 24m 33s
    1. Organizing the form elements
      7m 14s
    2. Formatting
      4m 48s
    3. Modifying the form through its properties
      6m 56s
    4. Adding a header and some polish
      5m 35s
  4. 1h 2m
    1. Introducing form controls
      3m 48s
    2. Using lines and rectangles
      2m 48s
    3. Organizing screen space with tabs
      4m 47s
    4. Adding buttons
      5m 3s
    5. Linking to external content
      4m 15s
    6. Entering and selecting data
      5m 8s
    7. Controlling input with option groups
      6m 0s
    8. Attaching documents
      6m 49s
    9. Attaching images
      5m 8s
    10. Understanding the subform control
      4m 13s
    11. Adding charts
      7m 9s
    12. Linking controls
      7m 41s
  5. 21m 42s
    1. Creating the main menu
      8m 49s
    2. Adding a splash screen with startup options
      5m 35s
    3. Creating a customer form
      7m 18s
  6. 45m 20s
    1. Grouping and sorting data
      4m 36s
    2. Understanding report structure
      6m 12s
    3. Building reports from wizards
      5m 0s
    4. Building reports from queries
      6m 34s
    5. Formatting conditionally
      6m 59s
    6. Calculating fields
      4m 35s
    7. Adding the finishing touches
      4m 49s
    8. Populating pre-printed documents
      6m 35s
  7. 15m 8s
    1. Printing reports
      3m 6s
    2. Tweaking the design
      7m 10s
    3. Automating the workflow with macros
      4m 52s
  8. 58s
    1. Next steps
      58s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
Please wait...
Access 2010: Forms and Reports in Depth
3h 7m Intermediate Feb 14, 2012

Viewers: in countries Watching now:

Discover how to manage data entry and reporting tasks more efficiently using Access 2010. Author Adam Wilbert presents lessons on designing forms, organizing and displaying data with form controls, creating flexible queries, and building a form-based navigation system. The course also shows how to build reports from wizards and queries, highlight important data with conditional formatting, and automate reporting processes with macros.

Topics include:
  • 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
Subjects:
Business Forms Databases
Software:
Access Office
Author:
Adam Wilbert

Formatting conditionally

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.

There are currently no FAQs about Access 2010: Forms and Reports in Depth.

Share a link to this course
Please wait... Please wait...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Access 2010: Forms and Reports in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked