Thanks for the on-going support! I'm trying to do something that seems simple but I'm having a problem!
I have a form (see attached) where there is a box called 'CV Date of Renewal' which has a date in. If the date is in the
future, I'd like the 'CV Information' Box to go Green and display Valid.
If the Date has passed I'd like the 'CV Information' Box to go Red and say 'Expired'.
If the Date is due to expire in a month I'd like the 'CV Information' Box to go Yellow and say 'Expires Within One (1)
There are three such boxes similar to the 'CV INFORMATION' Box, called Q INFORMATION and Z INFORMATION. These two have the
same rules as CV INFORMATION, in that there is a box called 'Q Date of Renewal' and 'Z Date of Renewal'. If all three of
these boxes are Green and Valid, I'd like another box, called 'Permission' to be Green and say 'Yes'. If there is one or more
boxes that are red and 'expired' I'd like the 'permission' box to be Red and say 'No'. If there are yellow boxes, (one month
expiry) as long as they appear alongside green boxes permission is 'YES'. However, if they appear alongside Red boxes
permission will be 'No'.
I'd like this to be an automatic update, so that if, for example, the CV date of renewal passes the box will turn to Red from
yellow, having previously been green.
I did a similar thing in excel but I cant get it to work on access: this is what I have (for the individual boxes - in this
=IF(CV Date of Renewal>TODAY()+30, "Valid", IF(CV Date of RenewalTODAY(),Z Information>TODAY()),"YES","NO")
I have done conditional formatting for all boxes mentioned, based on the information already on the table, but I'm not sure
how to do the parts where the boxes will update themselves based on the change of time. I'm also not sure how this will
appear in a table, if needed, or in VBA etc so I'd be grateful for your wisdom!
PLEASE HELP! THANKS :-)