Help changing field's colour if the value is negative? 1

[Keetso]

Hi.

I know I saw an answer to this question somewhere on the forums but can't seem to find it.

When printing a report (either preview or hard copy), how do I change the field's colour to red if the value is negative?

For example, if the report is as follows:

Name CompanyA CompanyB CompanyC Total

John Doe 100.00 -100.00 50.00 50.00
Jane Smith -50.00 200.00 100.00 300.00

Thus, For John Doe, the values for CompanyA and CompanyC would remain black but the value for CompanyB would be red. The same for Jane Smith's total for CompanyA.

Can someone point me in the right direction?

TIA!

K

 

[Bchemist]

this can be done in the "on format" function for the report section.

i.e. If this is the detail section of the report it would be something like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If Me!txtValue <0 then
         Me!txtValue.BackColor = vbRed (or some color ie 255)
   Else
         Me.txtValue.BackColor = vbWhite (etc)
   End if
End Sub

Also you could set the backcolor to red at design time and change the BackStyle from transparent to normal and vice versa

 

[dhookom]

There is generally no reason for coding. You can set the format property. This example straight from Help:
Format: $#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Keetso]

Hi.

BChemist: That's basically the code I remember, but it's not working. I'm getting a VB error.

Duane: I tried that as well and the colour changes fine in the tables, but it's not being passed to the report. I will continue playing with it though as I think this is the way to go.

Thanks again to all!

K

 

[Bchemist]

Keetso, what is the VB Error you are getting?

It might have to do with your naming. For example if your field name is Me!Value rather than Me!txtValue it might be looking at the actually data field rather than the text label. Also, make sure that your value is a numeric (i.e. not a text value, from the query) You could try

If CDbl(Me!txtValue) < 0 then
...
end if

 

[dhookom]

Keetso,
You would need to set the format property in the report controls/text boxes.

Format: $#,##0.00[Green ];($#,##0.00)[Red ];&quot;Zero&quot;;&quot;Null&quot;
Note: remove the spaces following the colors in the above line.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.