Be able to control the display format of numeric data including number of decimal places, currency symbol, percentage

ICT 0417 – Databases

Topic 18 – Controlling the Display Format of Numeric Data

In a database (or spreadsheet) you can change how a numeric value is shown without changing the value that is stored. This is essential for:

• Making reports easy to read (currency symbols, thousands separators, % signs, negative‑value masks).
• Meeting the Cambridge IGCSE 0417 syllabus requirements for data types, display masks and e‑safety.
• Ensuring calculations, sorting and searching use the underlying numeric data, not the formatted text.

1. Numeric Data Types in a Database

When you create a table you must select the appropriate data type for each field. The chosen type determines which display masks are available in the field’s Properties → Format (or Number) tab.

2. What Is a Display Mask?

A display mask (also called a format mask) tells the DBMS how to present the stored numeric value. The mask does not alter the underlying data; it only changes the visual representation.

Core mask symbols

3. Setting a Display Mask in a DBMS (Access, LibreOffice Base, or any other DBMS with a “Field Properties → Format” pane)

1. Select the table in Design view.
2. Click the field you wish to format.
3. Open the Field Properties pane.
4. Choose the appropriate Data type (Number, Currency, or Percentage).
5. On the Format (or Number) tab, type the required display mask (see examples in Section 4).
6. Save the table – the underlying values stay unchanged; only the visual representation is altered.

Note: The syllabus allows any hardware platform, operating system and application, so the same steps apply to MySQL Workbench, Microsoft SQL Server Management Studio, or other tools that expose a format‑mask field.

4. Common Display‑Mask Examples

4.1 Decimal‑Place Formats (Number fields)

4.2 Currency Formats

4.3 Percentage Formats

5. Effect of Formatting on Calculations, Sorting, Filtering & Searching

• Calculations: All arithmetic uses the underlying numeric value, not the formatted text. Example – a field shown as $12.50 is still stored as 12.5 for any query or expression.
• Sorting: The DBMS sorts on the stored number, so a field formatted as "£"#,##0.00 will sort correctly from smallest to largest.
• Filtering: Criteria must refer to the raw value. WHERE Price > 1000 works even if the field displays £1,200.00.
  

  Common pit‑fall: WHERE Price > "\$1000" fails because the literal “\$” is not part of the stored data.

• Searching (text search): A text search for the currency symbol will only find matches if the search string includes that symbol. Numeric searches should always use the numeric value.

6. Calculated Fields that Use Display Masks

When a field is calculated, the DBMS uses the underlying numbers, then applies the mask you assign to the result field.

-- Example in Access (Query Design)
TotalPrice: [Quantity] * [UnitPrice]
-- Field definitions
Quantity   – Number (integer, format: 0)
UnitPrice  – Currency (format: "$#,##0.00")
TotalPrice – Currency (format: "$#,##0.00")

Even if UnitPrice is shown as $12.50, the calculation uses the exact stored value 12.5. The result TotalPrice is then displayed with the chosen currency mask.

7. Display Formats in Printed or Exported Reports

• The format mask you set for each field is automatically applied to the printed output.
• Always preview the report before finalising – the syllabus expects students to verify that labels and formats match the specification.
• When exporting to CSV or plain‑text, the formatting is stripped and only raw numbers are saved. Re‑apply a mask in the destination application if required.

8. e‑Safety & Protection of Sensitive Numeric Data

• Permissions: Restrict read/write access to fields that contain personal or financial information (e.g., salaries, student fees).
• Encryption / password protection: Use encrypted database files or password‑protected reports when sharing data outside the school.
• Masking in shared reports: When a report is distributed publicly, hide or round sensitive numeric columns (e.g., show “£xx,xxx” instead of the exact figure).
• Audit trails: Keep a log of who modified numeric fields that store confidential data.

9. Quick Reference – Common Mask Symbols (including negative‑number mask)

10. Applying Formats in a Spreadsheet (Excel, LibreOffice Calc)

1. Select the cell(s) or column you wish to format.
2. Right‑click ► Format Cells….
3. Choose a Category:

   • Number – set decimal places.
   • Currency – choose symbol, decimal places, and thousands separator.
   • Percentage – set decimal places.

4. In the Custom box you can type any of the masks shown in Sections 4‑6.
5. Click OK. The displayed appearance changes, but the underlying numeric value remains unchanged.

11. Practice Exercises (AO2 & AO3)

1. Enter the value 9876.543 and apply three different formats:

   • Two decimal places (Number) – what does it look like?
   • US dollars with a thousands separator and two decimal places – write the exact mask.
   • Percentage with one decimal place – write the mask and the displayed result.

2. The field Price contains the number 49.9. Show the displayed result when the format mask is "£"#,##0.00.
3. Explain what happens to the underlying data when you change a cell from 0.25 to a percentage format with zero decimal places. Include the effect on any calculations that use this cell.
4. In a table you have fields Quantity (Number) and UnitPrice (Currency). Create a calculated field Total (Currency) that multiplies the two. Show:

   • The display mask you would assign to Total.
   • The displayed result for Quantity = 8 and UnitPrice = £12.50.

5. Discuss two e‑safety considerations when exporting a report that contains a column of staff salaries formatted as currency.
6. New AO3 task: An existing field Cost is defined as Number with the mask 0.00. Change its data type to Currency and set the mask to "$"#,##0.00. Then re‑run a query that sums Cost. Explain:

   • What, if any, change occurs to the stored values.
   • Why the query result (total) remains the same even though the visual format has changed.

12. Summary

• Formatting changes only the visual representation; the stored numeric value remains unchanged.
• Choose the correct data type (Number, Currency, Percentage) when defining a field – remember that Number can be integer or floating‑point.
• Use 0 to force digits, # to hide unnecessary zeros, , for thousands, and literal symbols for currency or text. Include a negative‑number mask (positiveMask;‑negativeMask) when required.
• Decimal places, currency symbols, percentage masks, and negative‑value masks improve readability and satisfy syllabus reporting standards.
• All sorting, filtering and calculations operate on the underlying numeric value, not the formatted display.
• Always preview reports to confirm that the required format appears correctly.
• When sharing data, protect sensitive numeric fields through permissions, encryption, and appropriate masking.