If/Then that changes a fields backcolor

  • Thread starter Thread starter kdavis
  • Start date Start date
K

kdavis

If I have a field "x" and if it is empty, i want the field to be Green,
however, if the field has a date and that date is less than 7/1/09, I want
the field to be Yellow.

any ideas?
 
Have you looked into Conditional Formatting? (you didn't mention which
version of Access...)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
yes, i cant make it take into consideration if the field is empty. I am
looking at formatting some date fields so that as time nears the deadline,
the fields change color from green to yellow to red. I just cant figure out
how to make it work when the field is empty.
 
Again, depending on which version of Access you are using, you might be able
to put more than one condition on that field.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If I have a field "x" and if it is empty, i want the field to be Green,
however, if the field has a date and that date is less than 7/1/09, I want
the field to be Yellow.

any ideas?
Click to expand...

What does the 7/1/09 represent here? A fixed value of July 1, 2009 or
the current date, whatever that current date is?

Right click on the Date congtrol. Select Conditional Formatting.
Set Condition1 to
Expression Is
Write [ControlName] Is Null
Select as the back color Green.
Click on Add
Set Condition2 to
FieldValue Is
choose
Less than
Write in the next box
Date()
Select as the back color, Yellow.
Save the changes

The above Condition2 assumes you meant to use the current date to
compare the value against.
If you mean to always use 7/1/2009 to compare to, change Date() to
#7/1/2009#.
 
If I have a field "x" and if it is empty, i want the field to be Green,
however, if the field has a date and that date is less than 7/1/09, I want
the field to be Yellow.

any ideas?
Click to expand...

Try this

Private Sub txtTest_AfterUpdate()

With Me.txtTest
If IsNull(.Value) Then
.BackColor = vbGreen
ElseIf (IsDate(Me.txtTest)) And (.Value < #7/1/2009#) Then
.BackColor = vbYellow
Else
.BackColor = vbWhite
End If
End With

End Sub

The above code assumes that your field is bound to a textbox called
txtTest. You will also need to add or call this code from the
OnCurrent event, so that the value will be tested when navigating to
each record. You can also substitute Date() for the hard-coded date
value, to always test against the current date.
 
Try this

Private Sub txtTest_AfterUpdate()

    With Me.txtTest
        If IsNull(.Value) Then
           .BackColor = vbGreen
        ElseIf (IsDate(Me.txtTest)) And (.Value < #7/1/2009#) Then
            .BackColor = vbYellow
        Else
            .BackColor = vbWhite
        End If
    End With

End Sub

The above code assumes that your field is bound to a textbox called
txtTest.  You will also need to add or call this code from the
OnCurrent event, so that the value will be tested when navigating to
each record.  You can also substitute Date() for the hard-coded date
value, to always test against the current date.
Click to expand...

Sorry, I was thinking 'form' here, not 'report'. For a report, you
can use the same code, but put it in the OnFormat event of the report
section that contains the textbox
 
Your the winner!! Thanks so much for your help!
--
Kenny


fredg said:
If I have a field "x" and if it is empty, i want the field to be Green,
however, if the field has a date and that date is less than 7/1/09, I want
the field to be Yellow.

any ideas?
Click to expand...

What does the 7/1/09 represent here? A fixed value of July 1, 2009 or
the current date, whatever that current date is?

Right click on the Date congtrol. Select Conditional Formatting.
Set Condition1 to
Expression Is
Write [ControlName] Is Null
Select as the back color Green.
Click on Add
Set Condition2 to
FieldValue Is
choose
Less than
Write in the next box
Date()
Select as the back color, Yellow.
Save the changes

The above Condition2 assumes you meant to use the current date to
compare the value against.
If you mean to always use 7/1/2009 to compare to, change Date() to
#7/1/2009#.
Click to expand...
 
Your the winner!!  Thanks so much for your help!
--
Kenny

What does the 7/1/09 represent here? A fixed value of July 1, 2009 or
the current date, whatever that current date is?
Click to expand...
Right click on the Date congtrol. Select Conditional Formatting.
Set Condition1 to
Expression Is
Write [ControlName] Is Null
Select as the back color Green.
Click on Add
Set Condition2 to
FieldValue Is
choose
Less than
Write in the next box
Date()
Select as the back color, Yellow.
Save the changes
Click to expand...
The above Condition2 assumes you meant to use the current date to
compare the value against.
If you mean to always use 7/1/2009 to compare to, change Date() to
#7/1/2009#.
Click to expand...
Click to expand...

You are welcome. I'm glad it solved your problem.
 
Back
Top