I’ve created a query that contains an “iif” function and have created a report based off this query. I am struggling to find a way to calculate the sum of the field that uses the iif function in my report
Basically, I have my Courses table which contains a list of courses taught, the instructor name, how many credits the course is worth etc. and I have a Stipend Amount table which is linked to my Courses table through the number of credits the course is worth (Stipend amount is based on number of credits). Only courses taught outside of a regular teaching load get paid a stipend, so I only need these stipend amounts to show up if the “Load” field in the Course table reads “No”. If the “Load” field reads “Yes” then no Stipend is paid. I’ve created a query with a Stipend field that contains the following:
IIf([tbl_Courses].[Load]="yes","N/A",[Tbl_Stipend_Amount].[Stipend_Amount])
The query worked great, and now I’ve created a report that groups the courses by Instructor name to show a list of all courses taught by an instructor. When I try to add a text box to calculate the sum of the Stipend for all courses taught by an instructor: =sum([Stipend]) I get the error message “Data Type Mismatch in Criteria Expression”. Is it possible to calculate the sum of a field that uses an iif function?
Basically, I have my Courses table which contains a list of courses taught, the instructor name, how many credits the course is worth etc. and I have a Stipend Amount table which is linked to my Courses table through the number of credits the course is worth (Stipend amount is based on number of credits). Only courses taught outside of a regular teaching load get paid a stipend, so I only need these stipend amounts to show up if the “Load” field in the Course table reads “No”. If the “Load” field reads “Yes” then no Stipend is paid. I’ve created a query with a Stipend field that contains the following:
IIf([tbl_Courses].[Load]="yes","N/A",[Tbl_Stipend_Amount].[Stipend_Amount])
The query worked great, and now I’ve created a report that groups the courses by Instructor name to show a list of all courses taught by an instructor. When I try to add a text box to calculate the sum of the Stipend for all courses taught by an instructor: =sum([Stipend]) I get the error message “Data Type Mismatch in Criteria Expression”. Is it possible to calculate the sum of a field that uses an iif function?
