Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Color Coding Dates

    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.

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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.

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color Coding Dates

    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.
    Charlotte

  4. #4
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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.

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    Additionally...I am using Office 2000.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    Are you displaying the information in a form or in a report?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  7. #7
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color Coding Dates

    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.
    Charlotte

  9. #9
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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!

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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.

    <pre>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
    </pre>

    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  11. #11
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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.

  12. #12
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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.

    <pre>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
    </pre>

    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  13. #13
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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?

  14. #14
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    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! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  15. #15
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates

    <hr>you can specify other colors by setting the ForeColor = the hex value for that color<hr>
    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. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •