Simple Conditional Format Expression

[NickDenyer]

Hi Guys,

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) Month'.

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 case CV):

=IF(CV Date of Renewal>TODAY()+30, "Valid", IF(CV Date of Renewal<TODAY(), "Expired", IF(CV Date of Renewal-30<=TODAY(), "Expires Within One (1) Month", "")))


For the permissiond bar I had this:

=IF(AND(CV Date of Renewal>TODAY(), Q Information>TODAY(),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

 

[Trevor G]

You can do this with some VBA behind the form Event On Current. Open your form in design view and select the form properties, click the Event Tab and select the On Current Event, click the elipse button and select Code Builder this is where you can create the VBA Code:

Private Sub Form_Current()
If Me.CV_Date_of_Renewal.Value > Date + 30 Then
Me.CV Information.BackColor = vbGreen
Me.CV Information.Value = "Valid"
ElseIf Me.CV_Date_of_Renewal.Value < Date Then
Me.CV Information.BackColor = vbRed
Me.CV Information.Value = "Expired"
Else: Me.CV Information.BackColor = vbYellow
Me.CV Information.Value = "Expires Within One (1) Month"
End If
End Sub

 

[NickDenyer]

Trevor G - as far as I'm concerned, you're a genius but I expect nothing less from a fellow Staffordshire dwellar (Stafford now, formally Cannock!!!) this worked wonderfully!!!! :-D any idea of how to do my 'permission' box also? I'll have a crack at it based on this VBA you have supplied above.

If you wouldn't mind, could you also look at this thread:

- http://www.access-programmers.co.uk/forums/showthread.php?p=1158504#post1158504

I've been unable to get any help so far :'( but I think its because the way I wrote it looked like plagerism at first!

Thanks again, I'm extremely grateful, so have some Reputation points and thanks!!!

 

[Trevor G]

Cannock hay, nice place, hope stafford is just as nice for you!

Its the same principle as my previous IF statement. I have created a table with the same field names and on the form I added a label called lblPermissions and in the same place as the first IF Statement I have added the following.

If Me.CV_Date_of_Renewal > Date And Me.Q_Information > Date And Me.Z_Information > Date Then
Me.lblPermission.Caption = "Yes"
Me.lblPermission.BackColor = vbGreen
Else
Me.lblPermission.Caption = "No"
Me.lblPermission.BackColor = vbRed
End If

 

[Trevor G]

I have had a look at your other thread. I would rethink your calendar issue. Create a table that would Hold the Date and other field information that you want, you can then work with that as an entry form, then it might work via a cross table query to view the data like a calendar as a form month by month with controls on them to let you view or edit entries and add to them.

I have seen other peoples work in doing something like this, if I can recall an example I will post you the link.

 

[NickDenyer]

Yes lived literally on the back of Cannock Chase so can't complain! Stafford is beautiful too. Your second VBA worked a treat also I really can't thank you enough!

As for the calendar, any ideas or examples you know of please pass along as I really want to keep the idea of having that kind of calendar, I just don't know where to start!

Thanks again!!!!!!

 

[Trevor G]

Nick look here as an example to help get you started. As I suggested you need to create a table to hold the appointments etc. There is a price involved here but the first 2 parts are free and as you look at the steps it will give you a start point.

http://www.599cd.com/site/courselist/seminars/access-calendar/

I would also ask do you use CRM with Outlook as there are some templates that can be adapted.

 

[NickDenyer]

Cheers, Trevor. I've had a look at the website and I'll look through the videos. I'm hopeful I can get this sorted as it would really help me out a lot with something I'm working on.

I don't use CRM or outlook, but the template I have at the moment is what I'm looking to adapt.

Once again thanks for your help with the other issue; that part is coming along swimmingly now!!!!

Nick