Conditional format of text

[Misty]

I have a textbox, [Due Date] on a form. If the date in the field is greater than todays date, I need the text in the field to be bold red. If the date is less than or equal to todays date, then I need the text to remain as it is set in the properties, (bold black).

I've tried the below code with no change to the color:

Private Sub Form_Open(Cancel As Integer)
If Me.[Due Date] > Now() Then
Me.[Due Date].ForeColor = vbRed

Else

Me.[Due Date].ForeColor = vbBlack

End If
End Sub

I've also tried Conditional formatting with the first condition being If the date in the field is greater than todays date, the text in the field is to be bold red.

This didn't work either.

Any help would be greatly appriciated.

M

 

[Misty]

I got it working.

I got it to work! John Viescas had posted this method to another group and it worked fine!

Thanks anyway,

M


Private Sub Form_Current()

If Me![Due Date] < Now() Then
Me![Due Date].BackColor = 255
Else: Me![Due Date].BackColor = 12632256
End If

End Sub

 

[Mile-O]

Re: I got it working.

Private Sub Form_Current()

    Me.[Due Date].BackColor = IIf(Me.[Due Date] < Now(), 255, 12632256)

End Sub

Or on one line.

Are you sure it's the Now() function you want and not the Date() function though?

 

[Misty]

Darn, another problem...

In the field above it, there is another field named [Audit Completed]. If this field has anything in it, I need the color to stay as in the properties also. I tried this code and it didn't work:

Private Sub Form_Current()

If Me![Due Date] < Now() And
Me![Audit Completed] <> "" Then
Me![Due Date].BackColor = 255
Else: Me![Due Date].BackColor = 12632256
End If

End Sub


Also, I've been taught that the Iff statement can cause problems and should be avoided. I forget why. Whatcha think? And what would using the Date() instead of Now() do better?

Thanks again,

M

 

[Mile-O]

Re: Darn, another problem...

I tried this code and it didn't work:

Private Sub Form_Current()

If Me![Due Date] < Now() And
Me![Audit Completed] <> "" Then
Me![Due Date].BackColor = 255
Else: Me![Due Date].BackColor = 12632256
End If

End Sub

That's because you are testing if [Audit Completed] is a null string but if it's got no value in it thn t is simply going to be Null.

Therefore you'd say: IsNull(myfield) = False rather than <> ""

Also, ditch the ! and use the . as it's more efficient.

And if these are textboxes then you'd be best to prefix them with txt and lose the spaces in their names. Believe me, it's best practice as it reduces loads of problems that you won't want going forward. Using a prefix and omitting the spaces means you won't have to include those horrible square brackets.

I've been taught that the Iff statement can cause problems and should be avoided. I forget why.

If you use a lot of them then they can be inefficient.
If you nest a lot of them within each other then they can be inefficient.
If you use them in queries then, depending on the amount of records returnd, they can be inefficient.
If you use one in a subroutine (which will also be compiled later on) then you can safely say there's not going to be a problem with it.

And what would using the Date() instead of Now() do better?

Nothing. Was just asking. If your field only stores a date then I'd use Date(). i.e. #21/05/04# and if it stored a full date with a time then I'd use Now() i.e. #21/05/04 12:34:02#

Private Sub Form_Current()
    If Me.[Due Date] < Now() And Not IsNull(Me.[Audit Completed]) Then
        Me.[Due Date].BackColor = 255
    Else
        Me.[Due Date].BackColor = 12632256
    End If
End Sub

Also, there is an enumerated constant for 255 that can be used instead. (vbRed) The basic colours have this. vbWhite, vbBlack, etc.

i.e.

Private Sub Form_Current()
    If Me.[Due Date] < Now() And Not IsNull(Me.[Audit Completed]) Then
        Me.[Due Date].BackColor = vbRed
    Else
        Me.[Due Date].BackColor = 12632256
    End If
End Sub

If you want to play with colours you can also specify the RGB value like this:

Private Sub Form_Current()
    If Me.[Due Date] < Now() And Not IsNull(Me.[Audit Completed]) Then
        Me.[Due Date].BackColor = RGB(255, 0, 0)
    Else
        Me.[Due Date].BackColor = 12632256
    End If
End Sub

 

[Misty]

With a minor change, it's working!

I guess I got you turned around. I had to take out the *And Not IsNull* and use *And IsNull* to make it work right.

Now, if the due date has expired AND the audit has not been closed, the due date is highlighted red. Perfect!

Thank you for all of your help.


'**Start of Working Code**
'**********************
Private Sub Form_Current()


If Me.[Due Date] < Now() And IsNull(Me.[Audit Completed]) Then
Me.[Due Date].BackColor = RGB(255, 0, 0)
Else
Me.[Due Date].BackColor = 12632256
End If


End Sub

'**********************
'**End of Working Code**