GoalWrite conditional formatting rules for a database form or report, and test which records they highlight.
New words
control · an item on a database form, such as a text box or a labeldesign view · the view where the layout and rules of a form or report can be changedrule · a condition and a format that is used when the condition is truehighlight · to make important data stand out, for example with colour or bold text
Explanation
Organisers often need to see the important records at a glance. Conditional formatting can highlight data on input forms and on reports. You make the rule in design view by choosing the control, the condition and the format. The format is applied only to records for which the condition is TRUE. Scroll through the records afterwards to test the rule. Do not use too many colours, because the page gets confusing. Some people cannot tell colours apart, so make highlighted data bold as well as coloured.
Part of the rule
Example
Control (field)
Genre
Condition
equal to Science
Format
bold, blue fill
Worked examples
A
B
C
D
E
F
1
BookID
Title
Genre
Pages
Year
OnLoan
2
1
River Maps
Adventure
210
2018
Yes
3
2
The Quiet Kite
Story
96
2020
No
4
3
Space Atlas
Science
320
2021
Yes
5
4
Tiny Machines
Science
148
2019
No
6
5
Desert Wind
Adventure
275
2016
No
7
6
The Baking Club
Story
184
2022
Yes
8
7
Rock and Rain
Science
412
2015
No
9
8
Night Train
Adventure
350
2020
Yes
The library wants Science books to stand out. The rule is Genre = Science, with a blue fill. The shaded rows are the records for which the condition is TRUE. (Ignore the letters and numbers around the edge of the grid. They are part of the picture, not of the table.)
A
B
C
D
E
F
1
BookID
Title
Genre
Pages
Year
OnLoan
2
1
River Maps
Adventure
210
2018
Yes
3
2
The Quiet Kite
Story
96
2020
No
4
3
Space Atlas
Science
320
2021
Yes
5
4
Tiny Machines
Science
148
2019
No
6
5
Desert Wind
Adventure
275
2016
No
7
6
The Baking Club
Story
184
2022
Yes
8
7
Rock and Rain
Science
412
2015
No
9
8
Night Train
Adventure
350
2020
Yes
A second rule highlights long books with Pages > 300. Notice that a record can match one rule, both rules or neither. Space Atlas (320 pages) and Rock and Rain (412 pages) are Science books that are long, so both rules apply to them. Night Train (350 pages) matches only the second rule.
Record
Pages
Pages > 300
Tiny Machines (before)
148
FALSE
Tiny Machines (after)
340
TRUE
Test your rule by changing the data. If Tiny Machines grows to 340 pages after a new edition, the condition Pages > 300 becomes TRUE for that record, and it is highlighted at once. This is how you check that a rule works for new data too.
Class activity
Highlighter test (no computer). Copy the Books table onto paper, or make a table of ten things, such as the age and height of toy animals. Ask a partner for a rule such as ‘Pages less than 150’ or ‘OnLoan equal to Yes’. Colour the matching rows with a highlighter pen or coloured pencil. Then change one value on purpose and say which row changes colour. Finally add a second rule in a different style, such as underlining, and check which rows get both rules.
Practice
1
The rule is Year < 2018, with a red fill. Which records are highlighted? Give their BookID numbers.
A
B
C
D
E
F
1
BookID
Title
Genre
Pages
Year
OnLoan
2
1
River Maps
Adventure
210
2018
Yes
3
2
The Quiet Kite
Story
96
2020
No
4
3
Space Atlas
Science
320
2021
Yes
5
4
Tiny Machines
Science
148
2019
No
6
5
Desert Wind
Adventure
275
2016
No
7
6
The Baking Club
Story
184
2022
Yes
8
7
Rock and Rain
Science
412
2015
No
9
8
Night Train
Adventure
350
2020
Yes
BookID 5 (Desert Wind, 2016) and BookID 7 (Rock and Rain, 2015). The year 2018 is not less than 2018.
2
Write a conditional formatting rule (field, condition and format) that makes books that are on loan stand out in orange.
Field OnLoan, condition equal to Yes, format orange fill (and bold). The rule is applied only to records for which OnLoan = Yes is TRUE.
3
Rule A: Pages > 200 in red. Rule B: Genre = Story in blue. Which BookID numbers get rule A? Which get rule B? Does any record get both?
Rule
Field
Condition
Format
A
Pages
> 200
red fill
B
Genre
= Story
blue fill
Rule A: 1, 3, 5, 7 and 8. Rule B: 2 and 6. No record gets both, because the Story books have 96 and 184 pages, and neither is above 200.
4
Why should highlighted records be bold as well as coloured?
Some people cannot tell some colours apart, and a black and white print-out would lose the colour. Bold text still stands out.