Format Report Field Based off Another Fields Value

[zigstick]

Hello, I'm using Excel/Windows 2000 and have what I think "should" be an easy thing to do, but can't figure it out.

I have a report that has an Expense field, and an Expense Paid field. I want the Expense field to go to Green when the ExpensePaid field is greater than zero. Any suggestions? thank you!

 

[traingamer]

Use conditional formatting based on a formula e.g. =(B3) > 0

This is the Access forum by the way.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[zigstick]

Thanks Traingamer! Just what I was looking for. Oh yeah, I meant to say Access, not Excel. Been bouncing back and forth so much between the two.

 

[zigstick]

Uhhh, after looking at this, that is an Excel answer as I had you thrown off track.

 

[SgtJarrow]

This should point you in the right direction.

You first have to set the BackStyle property of the control to Normal (It is Transparent by default).

Then this code goes in the Details OnFormat Event and will change the BackColor of the field only...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Const cLightGrey = 12632256
Const cWhite = 16777215

    If Me.[Expense Paid] > 0 Then
        Me.[Expense Paid].BackColor = cLightGrey
    Else
        Me.[Expense Paid].BackColor = cWhite
    End If

End Sub

If you want to change the whole detail line, just change the Me.[Expense Paid].BackColor to Me.Detail.BackColor and leave the BackStyle property of the controls as Transparent.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[traingamer]

You can use conditional formatting on an Access report as well.
Format -> Conditional Formatting

Expression is [txtExpensePaid].[Value]>0

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill