Office challenge: How can you apply formatting to an entire row, based on the contents of a single cell?

Test your skills against this Excel formatting challenge and learn the answer to last week's Access query challenge.
A client presented an interesting challenge this past week. She was using an Excel sheet to track projects and she wanted overdue projects to stand out. Using conditional formatting, she was able to shade a project's overdue date, but she wanted to shade the entire row, not just the actual date (a single cell). In other words, if the due date was past, she wanted the entire row to be bright red. How would you satisfy this requirement? Last week we asked… Why isn't your Access query returning all of the expected records? Many things could explain this situation, but I did specify that searching for a specific value and searching for everything but that value failed to return all the records. Assuming your first two criteria expressions are correct, there's only one possibility — null values. Nonsy was the first to mention null values. Iansoady, Youzer, and Tony Hopkinson also answered correctly. Let's look at a quick example using Northwind, the sample database that comes with Access. Specifically, the Customers table has 91 records total. The following query, which matches the ZIP (or postal) code 97219, returns 1 record. You might assume that the other 90 records have ZIP codes other than 97219, but would you be right? To find all the records where the ZIP value is not 97219, you might use this next query. However, if you were expecting it to return 90 records, you were wrong. It returns just 89. The only (the best) explanation is that one of the records doesn't have a ZIP value at all, and that possibility is easy to forget when querying data. Now, the null might not matter. Your needs will determine whether the missing record(s) are important. If you must account for all records, expand one of your queries to include null values. You can do so by adding an OR operator to the expression in the following form: Is Null OR value Is Null OR Not value where value represents the string, date, or value that you're searching for or excluding. This last query uses Is Null to return null values and every ZIP value other than 97219. This time the query returns 90 records; all records are accounted for. For database developers, this is a basic issue. However, it's a problem I see frequently and begs the much larger question of how to deal with null values in the first place. Do you allow null values in your tables? Feel free to start a discussion about the validity of null values.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks