Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Change a field based on date

Posted on 2012-08-20
22
Medium Priority
?
403 Views
Last Modified: 2012-09-13
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"
0
Comment
Question by:Joe
  • 12
  • 8
20 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38314523
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
 

Author Comment

by:Joe
ID: 38314533
Unfortunately, I am using Access 2003
I can live with the 3 condition limitation  - green - yellow - red
Thanks for any help with this
0
 

Author Comment

by:Joe
ID: 38314545
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Joe
ID: 38314547
table name is tblAccreditations
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38314555
Is this on a continuous form or a single form?  What is category?  Is this an option group?
0
 

Author Comment

by:Joe
ID: 38314600
Single Form
Drop down selection based on a query
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38314664
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
 

Author Comment

by:Joe
ID: 38316570
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38318965
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
 

Author Comment

by:Joe
ID: 38321945
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38323578
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
 

Author Comment

by:Joe
ID: 38330306
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38330327
Could you upload a database with just the objects in question?
0
 

Author Comment

by:Joe
ID: 38330739
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
 

Author Comment

by:Joe
ID: 38338654
Any luck with my test database?

Thanks!
0
 

Author Comment

by:Joe
ID: 38383441
Has this question been abandoned?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38389186
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
 

Author Comment

by:Joe
ID: 38391166
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38393235
I guess I didn't really test it out.  Try this version.
Test.mdb
0
 

Author Closing Comment

by:Joe
ID: 38395005
Thank You very much for your assistance.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question