A text box with a warning on a report

[fat controller]

I will try and explain this as best I can, but please feel free to ask if I don't make any sense.

I have a report which shows five year training records for staff; when they attain their qualification, they are given an expiry date for that qualification, and they must attend 5 courses over the five year period (so one each year) and after the fifth course their qualification is renewed for a further five years.

The report shows their expiry date, and shows the date(s) of any courses they have attended, with each of these dates in its own text box.

What I would like to do is to have a textbox or label that shows the word OVERDUE based on certain criteria being met:

If there is a date in the 'course date' field, then show nothing.
If the 'course date' is null, then check to see if the course is overdue - so for course 1, the date would be the date contained in the 'expiry date' field less 4 years; for course 2 that would be 'expiry date' less 3 years and so on.

Can this be done? Or am I barking up the wrong tree trying to do it in the report, and should I be doing via some sort of expression at query level?

 

[Ranman256]

yes, put this 'status' in the query,
IIF(DateDiff("yyyy","[ExpDate]","[TestDate]")>5,"Overdue","") as Status

drag the field onto the report

 

[fat controller]

You are a diamond, thank you. I am just shutting down for the night, but will do that tomorrow an post back how I get on.

Cheers

 

[fat controller]

OK - having some problems with this; when I try to run the query, I am getting a data mismatch error?

Here is the code as it is for one of the fields (I need five, with the criteria varying ever so slightly)

Status1: IIf(DateDiff("yyyy","[Current Card Expiry Date]","[C1_Attended_Date]")>4,"Overdue","")

This was lifted from design view, however I can post the full SQL for the line or full query if it helps?

 

[fat controller]

Found a workaround - instead of using the word overdue, all I did was create an unbound text box that shows the 'due' date that each session of the course is due to be completed by, and then using conditional formatting it goes red bold when overdue. Simple, but should do the trick.

Thanks again