Using the IIF function in Access and Creating Invoice-Type Reports

This tutorial willl use the Homeworks database: homeworks.mdb Download this file to your U: drive before using!

Using the IIF function:

The IIF function is used to create conditional values in Access. It is useful when two different values are needed in two different situations.

The format is

IIF( conditional statement, true value, false value)

• The conditional statement is something like Scores > 50, or Paid = true.
• If the conditional statement is true, the value of the IFF expression is the true value.
• If the conditional statement is false, the value of the IFF expression is the false value.

Examples:

Using IIF in a calculated field:

1. In the Homeworks database from above, create a query using all three tables and all the fields from each table that are unique and not keys Put the fields in this order: Last Name, First Name, Description, Score, MaxScore.
   
   
2. Run the query to make sure it works.
   
   
3. Add a new calculated field with the following as a field value:
   
   Warning: IIF( (Score/MaxScore) < 0.5, "Low Score!" , "")
   
   
4. This will create a new field called "Warning" and will display either "Low Score!" if the percentage score on any homework is less than 50%. Otherwise nothing will be displayed.
   
   
5. Run the query to make sure it works.
   
   
6. Save the query -- call it "HWScores"

Creating an Invoice-Style Report

An invoice-style report is essentially a master-detail report where each "customer" is on a new page.

To create an invoice-style report in the Homeworks database:

1. Create a report using the wizard.
   
   
2. Select the HWScores query from above.
   
   
3. Select all the fields.
   
   
4. Select LastName and FirstName as grouping fields. LastName should have the higher priority.
   
   
5. Sort the records by the homework description.
   
   
6. Select "Stepped" layout.
   
   
7. Choose whatever report style you'd like. . Check that it comes up showing a nice master-detail display.
   
   
8. Go to the Design mode for the Report.
   
   
9. Move the FirstName field control (the one that is in its own row -- not the column title) up to the same row as LastName.
   
   
10. Right click the FirstName and select Properties. Select All and then change Border Style to "Transparent".
    
    
11. Change the First Name font to Arial, 14 pt. You may need to resize it field control.
    
    
12. Anywhere on the grid, right-click and selecct "Sorting and Grouping".
    
    
13. On the row for "FirstName", select Group Header = No.
    
    
14. Right-click on the "LastName Header" bar and select "Properties"
    
    
15. Under "All", set "Force New Page" to "Before Section"
    
    
16. That's it!