Conditional Formatting for Multiple Field Requirements

[gracm25]

Good morning and Happy New Year,

I am a novice when it comes to VBA programming, but I feel pretty confident in normal Access functionality. I'm stuck when it comes to conditional formatting. I've developed a program that prints a report of how long a work order has been open. However, I would like to make WO's that have been open for less than 1 day to print in 1 color and those that have been open more than 1 day, but less than 3 days to print in another color. There are 2 catches to this:

1) The "# of days open" field displays the calculation as a text string

2) There is another field called MINOR_MAJOR that I want to play a role in this report.

If the WO is MINOR and is also less than 1 day, it should be bolded. If this WO is MAJOR and between 1 and 3 days, it should be bolded (another color). Anything outside this metric can be normal text. Here's the code I've been using to get the calculation to show as days, hours, minutes, and seconds. Can I build these conditions into this code? Thanks for any help!!

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*********************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function

 

[stopher]

In the On Format event for the detail section of the report, put the following code:

Me!myField.FontBold = False
Select Case dateTimeEnd - dateTimeStart

Case Is < 1  'interval less than 1 day
    Me!myField.ForeColor = vbRed
    If MINOR_MAJOR = "Minor" Then Me!myField.FontBold = True

Case Is >= 3  'interval greater than 3 days
    Me!myField.ForeColor = vbBlack

Case Else   'between 1 and 3 days
    Me!myField.ForeColor = vbGreen
    If MINOR_MAJOR = "Major" Then Me!myField.FontBold = True

End Select

I've ignored the fact that you are using a function to format your date interval and simply calculated it from the original fields.

The code above will apply the required formatting to the field named myField. you need to repeat the three sections inside the CASE for each field you want formatted.

hth
Chris

 

[gracm25]

Thank you, stopher!!! This worked perfectly!!

I'm still acquiring my VBA skills and I learn something new everyday. Showing me this information brings up 2 more questions, if you don't mind helping me.

1) This does make the work orders in question bold, but all work orders fitting each criteria are in that specific color. Is there a way that MINOR work orders > 1 day are not colored either? That would really put the icing on the cake.

2) How can I add an underline to those work orders that are already bold?

I really appreciate your help!!! Happy New Year!!

 

[stopher]

1) This does make the work orders in question bold, but all work orders fitting each criteria are in that specific color. Is there a way that MINOR work orders > 1 day are not colored either?

2) How can I add an underline to those work orders that are already bold?

So if I understand the logic correctly (and I'm not sure I do) then you want:

______________Minor_____________Major
<1___________Red/Bold/UL_________Red
>1 & <3______Black_____________Green/Bold/UL
>3___________Black______________Black

I've re-written the code as this ....

Dim dblInterval As Double

dblInterval = CDbl(dateTimeEnd - dateTimeStart)

Me!myField1.ForeColor = vbBlack
Me!myField1.FontBold = False
Me!myField1.FontUnderline = False

If MINOR_MAJOR = "Minor" Then
    If dblInterval < 1 Then
        Me!myField1.ForeColor = vbRed
        Me!myField1.FontBold = True
        Me!myField1.FontUnderline = True
    End If
ElseIf MINOR_MAJOR = "Major" Then
    If dblInterval < 1 Then
        Me!myField1.ForeColor = vbRed
    ElseIf dblInterval >= 1 And dblInterval < 3 Then
        Me!myField1.ForeColor = vbGreen
        Me!myField1.FontBold = True
        Me!myField1.FontUnderline = True
    End If
End If

 

[gracm25]

stopher, this works perfectly. Thank you for all you assistance. Managers are going to be pleased with this report!!

Enjoy a prosperous New Year!