Crosstab query using conditional formatting on second value field

Question

I have a report based on a crosstab query (created from a select query) that lists employees and dates of various types of training they have taken.  The training topics to be reflected on the report are selected by the user.  Each training topic has its own expiration date which is calculated to show when each employee's training will expire.  We would like to use conditional formatting in the report to show if the training is within 60 days from expiring (yellow), 30 days from expiring (orange), or has expired (red).  I would guess that in order for the conditional formatting to be used with the expiration date, that the expiration date field must be included on the report, however, a crosstab query will not allow two value fields, and I need to keep the training date on the report.  I need to keep the report style like a matrix.  Any suggestions?

Answer 1

I think I figured out the answer to my own question.  May not be the most ideal, but it seems to work.  I created a calculated field in my select query (from which the crosstab is created), that combines a prefix code with the training date.  Using multiple IIf statements comparing the expiration date to the current date, the code indicates if it is expired, within 60 days, within 60 days, or is good.  Then my conditional formatting looks for those codes using the greater than operator.