CODEWITHSUNDEEP
ms-accessreportconditional-formattingcalculated-field
Liz Ramirez
Liz Ramirez

Reputation: 1

How do I count total number of employees with expired certificates in MS Access Reports

I'm creating an employee database of certificates w/ expiration dates. I want to print quarterly reports for trainers to knows how many students will be due for a refresher before they expire. With conditional formatting, I have displayed Red for expired (3 year limit on certificate); yellow for expiring w/in 90 days and no color for current. Now I would like to use a text box in MS Access Report to calculate how many employees are going to expire within 90 days and how many employees have already expired. For example, I want to display that 22 employees are expired and 12 are expiring in 90 days automatically every time the report is run. I tried using the expression below but there is an invalid syntax and I'm at a lost. Please help!!

[CourseName] < Now()-1095

=Sum(IIf([CourseName] <= Now()-1095,1,0))

Upvotes: 0

Views: 178

Answers (1)

Ashraf Fouad
Ashraf Fouad

Reputation: 133

For dates that are going to expire in the next quarter(ExpDate) is the course expiry date 

=Sum(IIf([ExpDate]<=DateAdd("q",1,Date()) And [ExpDate]>=Date(),1,0))

& for expired dates 

=Sum(IIf([ExpDate]<Date(),1,0))

Upvotes: 0

Related Questions