Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Color Coding Dates (Access 2003)

    (Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)

    Hello, I have been working with an old database that I created several years ago and have a nagging problem. I posted a thread here (<post#=46644>post 46644</post#>) about wanting to color code dates in a report form so that it would show recurring training dates as certain colors depending on when they would come due, i.e. Blue 60 days out, Green 30 days out, Red Due/Over-Due. Below is the code that I received from Shane Sargent that I placed it in the "Module" for the specified report. I have around 12-14 fields on this report each using a block of this code. Some fields will be null. It has worked for the most part, but I am finding some dates with the incorrect color for the range that they fall in. If anyone would look over the code and let me know if there is a better way of doing this or maybe I am using it in the wrong spot, I don't know. Any help would be greatly appreciated.

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Color Coding Dates (Access 2003)

    If you have multiple blocks of code within the Detail_Print event procedure, you shouldn't use Exit Sub if one of the dates is Null - you'd skip all code for other controls below it. In fact, you don't need the Exit Sub at all here:

    If Len(Trim(Me.txtDueDate) & "") > 0 Then
    'there is a due date specified, so calculate
    'how many days out you are, and format accordingly.
    dteDue = Me.txtDueDate.Value
    ...
    End If 'Len(Trim(Me.txtDueDate) & "") > 0

    If Len(Trim(Me.txtOtherDate) & "") > 0 Then
    'there is another date specified, so calculate
    'how many days out you are, and format accordingly.
    dteDue = Me.txtOtherDate.Value
    ...
    End If 'Len(Trim(Me.txtOtherDate) & "") > 0

    etc.

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates (Access 2003)

    Thank you for replying so fast. I won't be in the office until Monday, but will put your suggestion to work as soon as I get there. So, do you think this could be the cause of the errors?

    Thanks again!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Color Coding Dates (Access 2003)

    Yep, I think exiting the event procedure prematurely causes records to display the color intended for a previous record.

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates (Access 2003)

    This worked perfectly. Thank You so much for your help. I am forwarding this to several other sections who are using this same code. I really appreciate all you do for us learners of the Code and pray you have a wonderful day.

  6. #6
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates (Access 2003)

    HansV, I have another question regarding conditional color coding. I have two fields (LOAC) and (LOAC_1). LOAC is a date field and will be color coded using the code that you have expertly troubleshot for me, however, LOAC_1 is a yes/no field and I would like to have it reflect a RED check mark on the report under the following conditions: LOAC is Null and LOAC_1 is Yes. Is this done in the same visual basic screen or can this be accomplished another way? Your help is greatly appreciated.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates (Access 2003)

    You can't change the color of a checkbox.
    The only way would be to create your own graphic file with a red check mark and display it when it is needed.
    Francois

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Color Coding Dates (Access 2003)

    As Francois notes, you cannot color a check box. There are fonts that contain a check box character, for example Wingdings.
    - Replace the check box with a text box.
    - Set its Control Source to =IsNull([LOAC]) And [LOAC_1]
    - Set its Border Style property to Transparent.
    - Set its font to WingDings.
    - Set its Format property to <code><!t>[Red]<!/t>

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Color Coding Dates (Access 2003)

    I know that you have a solution that works now by not exiting too early
    But...I think this would be better placed in the onformat event rather than the onprint event of the report
    Andrew

  10. #10
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates (Access 2003)

    This may be a stopper for me then. Are you stating that I need to change the field from a yes/no field to a text field just to make this work? If so then I will probably pass on this. As mentioned in earlier posts I have a training database that has date fields for each training event that our unit provides to our personnel. The way I have it set up is to use Yes/No fields next to each date field to identify if that specific training event is required for that person (not all personnel require the same training). I then have multiple queries set up using the yes/no fields in conjunction with the date fields to count the number of personnel that are required for an event and checking to see how many of those are actually current (not over due). To flag these records for over due dates is easy now that I have the proper code, but when a required event has a null value in the date field, then there is no date to color, so I was going to have the yes/no field display next to the event on the report and color code it if the date was null. If there is a way of performing this just within the report then I would love to read more. If this is not possible I can make do with what I have; I am just trying to make this more user friendly and to ensure that we don't miss someone who requires training. Any help would be great.

    Thanks,

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Color Coding Dates (Access 2003)

    > Are you stating that I need to change the field from a yes/no field to a text field just to make this work?

    No, most definitely not! The field should remain a Yes/No field, but instead of using a check box to display it in the report, use a text box. You do this by placing a text box on the report (from the Toolbox), then setting its Control Source, Format and Font properties as indicated in my previous reply.

  12. #12
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates (Access 2003)

    I did as you mentioned and the results were a bit off. The check did appear red, however, I may have been unclear as to when this check should appear. It would only appear when there isn't a date in the date field, but the event is required, or in better terms: [LOAC] IsNull and [LOAC_1] IsNotNull. I tried using the control source of: =IsNull([LOAC]) And =IsNotNull([LOAC_1]), but I had some records not display the check when it was required, and other records did display the check when it wasn't needed. This was the same for the code that you provided. Does this make sense?

    Thanks,

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Color Coding Dates (Access 2003)

    What are the data types of LOAC and LOAC_1 ?

  14. #14
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color Coding Dates (Access 2003)

    LOAC stands for "Law of Armed Conflict" should be populated with the date the individual is due for their annual refresher training. LOAC_1 is just a yes/no check box to indicate if this is actually required for the selected individual. If no then no date required (Like for contractors and civilians).

    LOAC = Date Field (Medium Date)
    LOAC_1 = Yes/No

    Thanks,

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Color Coding Dates (Access 2003)

    A Yes/No field is never null, it's either False or True. The expression

    =IsNull([LOAC]) And [LOAC_1]

    from one of my previous replies should do what you want.

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
  •