Color Coding Dates

Topic

New Reply

I have a database that tracks dates for training and immunizations for members in my unit. I need to have the report display the due dates for these items in color depending on how far out their due dates are.

Quite a while ago I remember reading about a script that would enable dates to be color coded for just such a need. however, I don’t know where I put that information.

The goal that I have is to identify (by color) when a due date is within 60 days out (Blue), 30 days out (Yellow), and then Today’s date/Over Due (Red).

If someone could tell me the procedures I would be extremely grateful.

Reply | Quote

Replies

If you have Access 2000, you can use Conditional Formatting. If you have Access 97, you’ll have to write some code to check the date and format the textbox based on its value. This would go in the Detail_Format section of the code (assuming your textboxes are in the Detail section of the report).

Post back if you need further assistance.

Reply | Quote

I’m not terribly impressed with Access 2000’s conditional formatting, so I generally use the 97 code-based approach even in 2000–it’s far more reliable.

Reply | Quote

I understand what you are both saying, however, I am REALLY new to VB and could use some real specifics. I did build this data base, but I still use only macros and the basic tools. I’m not especially sure of how to place in VB code.

Sorry if I sound too green on this subject.

Reply | Quote

Additionally…I am using Office 2000.

Reply | Quote

Are you displaying the information in a form or in a report?

Reply | Quote

Report Form! The report lists personnel who are coming due for training within the next 90 days. I have it running very well, I just need to have it come up in color.

Reply | Quote

Forms and Reports are two different things and the answers are going to be different depending on which one you’re using. Reports are intended to be printed, while forms are interactive with the user on the screen. “Report Form” is meaningless, so please tell us which one it is.

Reply | Quote

OK…I would like information on a Report. If it would be easer if I had access color code in a form then I would do it that way. I’m not picky right now…I would use a query but I wouldn’t know where to start with it.

I appreciate your patience with this.

Thanks!

Reply | Quote

Let’s assume you have the due date in the detail section of a report, and further assume that the name of the text box that shows the due date is txtDueDate. Right click on the Detail section of the report, choose Properties, and insert the following code for the OnPrint event on the Event tab. This should get you to spec from your original post, but you might reconsider your use of yellow – it’s pretty ugly!

If you have questions about what’s going on in the code chunklet, post back and I’ll see what I can do to answer them.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim dteDue As Date
Dim dteToday As Date
Dim intDiff As Integer

dteDue = Me.txtDueDate.Value
dteToday = Now()

intDiff = DateDiff("d", dteToday, dteDue)

Select Case intDiff
    Case Is <= 0
        Me.txtDueDate.ForeColor = vbRed
    Case 1 To 30
        Me.txtDueDate.ForeColor = vbYellow
    Case 31 To 60
        Me.txtDueDate.ForeColor = vbBlue
    Case Else
        Me.txtDueDate.ForeColor = vbBlack
End Select

End Sub

Reply | Quote

Thank You very much…I think this is just what I needed. Now, I can use this as a template for each of the fields that I have in the detail right? By just changing txtDueDate to the field name? Meaning I can have multiple date fields show as colors in the same detail? Or would it have to be a more laborious code, i.e. several “Private Sub…” in the detail?

Sorry about all the questions…I just want to do this right.

By the way…there are many records that do not have a due date posted, “Null”. How do I still have this code continue when it encounters a Null value?

Again, thanks for all your help.

Reply | Quote

Questions are good! The presence of null values was obviously something I hadn’t considered in the first cursory pass. This chunklet first tests to see if there is a value in the control; if there isn’t, it steps out of the procedure; if there is, it goes about its merry way and applies the formatting.

You can apply this to more than one control in the detail section by stacking the If-Then-Else code block one on top of the other and changing the control name in each block accordingly. I didn’t do so in this example for readability purposes, but did indicate in a comment where you can.

I’m sure that there is a more effecient and elegant way to go about this, but this should hopefully get the job done for you.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim dteDue As Date
Dim dteToday As Date
Dim intDiff As Integer

'If there is no due date specified
If Len(Trim(Me.txtDueDate) & "") = 0 Then
    'exit the sub
    Exit Sub
'there is a due date specified, so calculate
'how many days out you are, and format accordingly.
Else
    dteDue = Me.txtDueDate.Value
    dteToday = Now()

    intDiff = DateDiff("d", dteToday, dteDue)

    Select Case intDiff
        Case Is <= 0
            Me.txtDueDate.ForeColor = vbRed
        Case 1 To 30
            Me.txtDueDate.ForeColor = vbYellow
        Case 31 To 60
            Me.txtDueDate.ForeColor = vbBlue
        Case Else
            Me.txtDueDate.ForeColor = vbBlack
    End Select
    
End If 'Len(Trim(Me.txtDueDate) & "") = 0

'this is where you can have another block for another control.

End Sub

Reply | Quote

Works perfectly, Thank You so much. You were right about the “Yellow” too. What other colors are available. I tried putting in Orange, but it didn’t work. Have any suggestions?

Reply | Quote

Excellent! I’m glad it’s working out for you.

For color constants, you have the following choices: black, blue, cyan, green, magenta, red, white, and yellow. I think, but am not sure, that you can specify other colors by setting the ForeColor = the hex value for that color. As for what colors would look good, it’s best that I don’t issue an opinion…seriously…colors aren’t exactly my strong suit! Ask my girlfriend who says I shouldn’t go shopping by myself!

Reply | Quote

[indent]

---

you can specify other colors by setting the ForeColor = the hex value for that color

---

[/indent] I have done this in the past. Neat little tip for you re: selecting a suitable color. If you use the … build on Fore Color for a control, it will bring up the pallete for you to select from (or indeed you can create your own custom colour). Once you have a color you like, the value will appear in the Fore Color property. Set the color variable in your code to this value and there you go.

Reply | Quote

Thanks for the color info. I will start playing around with them.

HOWEVER, I have proceded to use this new data to color code all my reports “60 Day Training Report” & “30 Immunizations Due Report” (1 each for maintenance personnel and 1 each for pilots). I am now getting a run time error in ONE of the reports when I want to page down to the second page. The Debug screen identifies the following as the culprit:

intDiff = DateDiff(“d”, dteToday, dteDue)

I don’t see the problem unless intDiff is retaining data from continued use. I have 5 controls in each detail that I am color coding and have stacked the If/Thens as you said, changing only the control names where required.

Does the intDiff need to be “zeroed” out or reset to a default before each If/Then?

I think that this may…I say may…be the culprit in another anomaly. Several dates are showing up “Red” on several of the reports when the due date isn’t until 2002. Not very many are showing up like this, but this does cause a little wondering as to what I need to change/correct.

Other than the specific items mentioned above, the reports are working just fine.

Any Thoughts?

Reply | Quote

I thought of another possibility…does the date information have anything to do with it. Meaning, I use Medium Date format for the military, i.e. 19 Jun 01 for today.

I am assuming that when I see/type 02 for the year, the computer is putting the information in as 2002. How can I check for that. I have also noticed several dates that ARE within the date ranges that we’ve specified and that they are NOT being color coded. Again, it brings me back to the date. How can I verify the correct years are being referenced?

Reply | Quote