Do it now activity

1. Do it now activity Understand how to create calculated fields on Forms Understand the purpose of automation of processes Learning objectives How will you create a simple query that returns all of the students details? Once you have logged on open your database and create the query If statement IsNull Message Box Security Password Data protection act

2. Understand how to create calculated fields on Forms Understand the purpose of automation of processes Learning Objectives

3. By the end of the lesson you will be able to … Create calculated field to create a running total and generate a date based on a previous date Generate age based on DOB and auto generate group based on age

4. Generate age based on DOB field =Fix(DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))) Replace both [DOB] fields with the name of your date field that you want to generate the age from This expression should be placed on your default value of the field you want the age to appear in You will need to have a requery macro set up to run on lost focus of the last field you require the user to type in

5. Iif statement =IIf([Age]>30 And [Age]<=60,"Group3“,"No Group") Condition checks if age is between two parameters Group the person is assigned to if condition is met Group the person is assigned to if condition is not met This iif statement is used to generate the name of the group that the person will be assigned to based on their age. [Age] is the name of the field that holds the age that you will base the iif statement on – change this field name as necessary This expression should be placed on the default value of the field that you want to store the group that the person is auto assigned to. You will need to have a requery macro assigned to the last field you want the user to type in for this to update and show in the field

6. Nested iif statement =IIf([Age]>30 And [Age]<=60,"Group3", IIf([Age]>20 And [Age]<=30,"Group2", IIf([Age]>=10 And [Age]<=20,"Group1", "No Group"))) Nested version of the iif statement which checks several conditions and assigns to a choice of 3 groups based on these conditions If no conditions are met the user is assigned to No Group

7. Calculated control – running total Just add the two fields that you want to add together with a + symbol between them To access the expression builder click on … next to control in the data tab of the fields property sheet

8. Dmax to find latest payment date based on the ID of the student selected on a form Field name DMax("[Payment Date]","[TBL_Payments]","[Student ID]=‘” & [ID] & "'") Student ID on the table Table name Student ID on the form This expression will also need to go into the calculated control of the field – click on … next to the control property of the field where this data should be displayed

9. Add 1 month to the latest payment date of the student selected on the form Expression name month Add 1 =DateAdd("m",1,DMax("[Payment Date]","[TBL_Payments]","[Student ID]=‘ " & [ID] & “ ‘ ")) Adds 1 month to the latest payment date stored in the table

10. Update query to update the amount each student has paid Create a query based on TBL_Student – make it an update query and set it to update TBL_Student Test the query by selecting a student on FRM_Payment and adding a payment – run the query it should update the currently paid value in TBL_Student Create a button on FRM_Payment and add an openQuery macro to run the update query when it is clicked Update this field to the value in the field called runningTotal on FRM_Payment Update this on the student with the same ID as the student selected on FRM_Payment

11. Append query to add new payment to TBL_Payments Create an append query that appends data to TBL_Payment Create the 4 fields shown Test the query by creating a payment on FRM_Payment and running the query – it should add a payment to your TBL_Payment Add a second openQuery macro to your button on FRM_Payment to run the query

12. Learning objectives Understand how to create calculated fields on Forms Understand the purpose of automation of processes