Change a field based on date

I have a table that stores an expiry date for a certification. (field name = expiry_date)
I want to automatically assign a color to the background of the expiry_date field and a category based on the date:

Examples:

a date that is over 90 days in the future shows green and selects the category  "current"

a  date within the next 90 calendar days shows yellow and selects the category "expiring"

a date prior to today shows red and selects the category "expiring"

an empty date field shows orange and selects the category "unknown"
JoeAsked:
Who is Participating?
 
IrogSintaCommented:
I guess I didn't really test it out.  Try this version.
Test.mdb
0
 
IrogSintaCommented:
You can use Conditional formatting if you're using Access 2010.  Prior to this version, you can only apply formatting to up to 3 conditions.
0
 
JoeAuthor Commented:
Unfortunately, I am using Access 2003
I can live with the 3 condition limitation  - green - yellow - red
Thanks for any help with this
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JoeAuthor Commented:
a date that is over 90 days in the future shows green and selects the category  "current"

a  date within the next 90 calendar days shows yellow and selects the category "expiring"

a date prior to today shows red and selects the category "expired"
0
 
JoeAuthor Commented:
table name is tblAccreditations
0
 
IrogSintaCommented:
Is this on a continuous form or a single form?  What is category?  Is this an option group?
0
 
JoeAuthor Commented:
Single Form
Drop down selection based on a query
0
 
IrogSintaCommented:
Here's some code you could try out:
    Select Case IIf(IsNull(Me.expiry_date), "Blank", DateDiff("d", Date, Me.expiry_date))
        Case "Blank"
            Me.Category = "Unknown"
            Me.expiry_date.BackColor = RGB(255, 153, 0)
        Case Is < 0
            Category = "Expiring"
            Me.expiry_date.BackColor = vbRed
        Case 0 To 90
            Category = "Expiring"
            Me.expiry_date.BackColor = vbYellow
        Case Is > 90
            Category = "Current"
            Me.expiry_date.BackColor = vbGreen
    End Select

Open in new window

0
 
JoeAuthor Commented:
How can I incorporate the code you provided with the utility I am presently using to select the Expiry date?

 
Private Sub cmdAccredExpiryDate_Click()

    Me.AccredExpiryDate = ReturnDate(SMALL,Me.AccredExpiryDate)

End Sub

 

========================

 

Select Case IIf(IsNull(Me.AccredExpiryDate),"Blank", DateDiff("d", Date, Me.AccredExpiryDate))

        Case "Blank"

            Me.AccredType = "(04) Unknown"

            Me.AccredExpiryDate.BackColor = RGB(255, 153,0)

        Case Is < 0

            AccredType = "(03) Expired"

            Me.AccredExpiryDate.BackColor = vbRed

        Case 0 To 90

            AccredType = "(02) Warning"

            Me.AccredExpiryDate.BackColor = vbYellow

        Case Is > 90

            AccredType = "(01) Current"

            Me.AccredExpiryDate.BackColor = vbGreen

    End Select
0
 
IrogSintaCommented:
Try this:
Private Sub cmdAccredExpiryDate_Click()
    Me.AccredExpiryDate = ReturnDate(SMALL,Me.AccredExpiryDate)
 

    Select Case IIf(IsNull(Me.AccredExpiryDate),"Blank", DateDiff("d", Date, Me.AccredExpiryDate))
        Case "Blank"
            Me.AccredType = "(04) Unknown"
            Me.AccredExpiryDate.BackColor = RGB(255, 153,0)

        Case Is < 0
            AccredType = "(03) Expired"
            Me.AccredExpiryDate.BackColor = vbRed

        Case 0 To 90
            AccredType = "(02) Warning"
            Me.AccredExpiryDate.BackColor = vbYellow

        Case Is > 90
            AccredType = "(01) Current"
            Me.AccredExpiryDate.BackColor = vbGreen

    End Select
End Sub

Open in new window

0
 
JoeAuthor Commented:
The code appears to work for new records but if I change or blank the date info in the Accreditation Expiry Date field, it doesn't update the background color(s) or the Accreditation Status.

I have also tried to change the actual text colour in the fields as black is very hard to read on a red background but I've had no luck.

Here is my present code (I've updated some of the fieldnames to reflect the true code):

Private Sub cmdAccreditationExpiryDate_Click()
    Me.AccreditationExpiryDate = ReturnDate(SMALL, Me.AccreditationExpiryDate)

    Select Case IIf(IsNull(Me.AccreditationExpiryDate), "Blank", DateDiff("d", Date, Me.AccreditationExpiryDate))
        Case "Blank"
            Me.AccreditationStatus = "(04) In Progress"

        Case Is < 0
            Me.AccreditationStatus = "(03) Expired"
            Me.AccreditationExpiryDate.BackColor = vbRed
            Me.AccreditationExpiryDate.ForeColor = vbYellow
            Me.AccreditationStatus.BackColor = vbRed
            Me.AccreditationStatus.ForeColor = vbYellow
           
        Case 0 To 90
            Me.AccreditationStatus = "(02) Warning"
            Me.AccreditationExpiryDate.BackColor = vbYellow
            Me.AccreditationStatus.BackColor = vbYellow
           
        Case Is > 90
            Me.AccreditationStatus = "(01) Current"
            Me.AccreditationExpiryDate.BackColor = vbGreen
            Me.AccreditationStatus.BackColor = vbGreen

    End Select
    DoCmd.Requery
End Sub

Your assistance is greatly appreciated.
0
 
IrogSintaCommented:
Try adding the TEXT property in the Select Case statement:
   Select Case IIf(IsNull(Me.AccreditationExpiryDate), "Blank", DateDiff("d", Date, Me.AccreditationExpiryDate.Text))

Try experimenting with the background colors of your textbox.  Test different shades of red and when you find one that works, use the RGB numbers.  Just like the one I posted previously for a shade of orange:
Me.AccredExpiryDate.BackColor = RGB(255, 153,0)
0
 
JoeAuthor Commented:
I will continue to play wiht the text property in order to get the correct colors.

The bigger issue is that the code appears to work for new records but if I change or blank the date info in the Accreditation Expiry Date field, it doesn't update the background color(s) or the Accreditation Status.

Your assisitance is greatly appreciated.
0
 
IrogSintaCommented:
Could you upload a database with just the objects in question?
0
 
JoeAuthor Commented:
Uploaded.

As you will see, on entry of a new record, the colors will work but once entered, they populate all records with the same colors.

I need this to hold the color for the applicable record and change as the date changes.

I hope that makes sense?

Thanks in advance.
Test.zip
0
 
JoeAuthor Commented:
Any luck with my test database?

Thanks!
0
 
JoeAuthor Commented:
Has this question been abandoned?
0
 
IrogSintaCommented:
My sincere apologies.  I had email problems a couple of weeks ago and was unaware of responses from questions I had participated in.

I've attached your revised database.  Let me know if it works fine for you.
Test.mdb
0
 
JoeAuthor Commented:
The category is changing according to the date which is great but the background colors are not. They seem to be changing to whichever color is set last. Even when adding a record, the background color remains whichever color was set last instead of no color.
0
 
JoeAuthor Commented:
Thank You very much for your assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.