need red font for negative figures

[oldmags]

Hi everyone, I would like to have my currency field in a form to show red when negative, I can do it in Excel but cannot figure it out in Access.
Can anyone help please?
Thank you, Oldmags

 

[ypma]

Have you tried conditional formatting ?
in design mode of form click on the currency field and select conditional formatting ,
Field value: less than 0 select the colour your require

 

[missinglinq]

You can do this using the Format Property of the Control:

In the Format Property box

To show in Red only, i.e. 5

0;0[Red]

To show in Red with Negative Sign, i.e.-5

0;[Red]

To show in Red and inside of parenthesis, i.e. (5)

0;(0)[Red]

Linq ;0)>

 

[oldmags]

Thank you, that did work, but the result only shows when I click on the field. If I click on any other field, the answer does not show. I have this Expression in there =Sum(([Payment Received]+[Sales from Repositary]+[Special Collection Income])-([Cost of Van hire]+[Stipend for Priest]+[Travel Exp journey]+[Subsistance Cost]+[Recoverable Expenses])) to give me the balance, putting the format property in removed the currency format which does not really matter, but why will it not show without clicking on the field please?
Thank you. oldmags

 

[oldmags]

To YPMA! Sorry, I cannot get the hang of trying to reply!! I see a quick reply button but nothing happens I have an Expression in that field which is =Sum(([Payment Received]+[Sales from Repositary]+[Special Collection Income])-([Cost of Van hire]+[Stipend for Priest]+[Travel Exp journey]+[Subsistance Cost]+[Recoverable Expenses])) this gives me a balance, I tried Feild value less than 0.01 but it did not go red, or when I put zero in. Am I doing something wrong?

 

[Keiath]

Conditional formatting is the simple way to do this

Left click mouse and go from there

 

[ypma]

If you managed to get the Formatting box open there is a drop down box for you to set the colour you require .come back if still a problem

 

[oldmags]

Thank you, conditional format did work, but the result only shows when I click on the field. If I click on any other field, the answer does not show. I have this Expression in there =Sum(([Payment Received]+[Sales from Repositary]+[Special Collection Income])-([Cost of Van hire]+[Stipend for Priest]+[Travel Exp journey]+[Subsistance Cost]+[Recoverable Expenses])) to give me the balance, putting the format property in removed the currency format which does not really matter, but why will it not show without clicking on the field please?
Thank you. oldmags

 

[ypma]

Try removing the sum and put in the nz which will put a zero in the calculation if the field is Blank .
=nz(([Payment Received])+nz([Sales from Repositary])+nz([Special Collection Income])-nz(([Cost of Van hire])+nz([Stipend for Priest])+nz([Travel Exp journey])+nz([Subsistance Cost])+nz([Recoverable Expenses]))

As for currency in the properties of the balance field in Design mode ,click format and drop down to currency, hopefully that should do it .

Check the spelling to ensure all fields in the expression are the same as the name of the fields in the Form ,
Let me know how you get on

 

[oldmags]

I tired that but it didn't work, it produced the usual #Name? When I did it the way from another tip, You can do this using the Format Property of the Control:

To show in Red with Negative Sign, i.e.-5 do 0;[Red]
It did work, but only showed in the field when I was in that field, when I went to the next field the data vanished and was only visible when I clicked in that field.

:banghead:

ouch!!
Thanks
Oldmags

 

[ypma]

#Name? normally means a spelling mistake or a field it does not recognize. If you let me know what version of Access you are using i will produce a demo .



R

 

[oldmags]

I am using Access 2010. I appreciate your help, thank you

 

[ypma]

Sorry for delay in getting back to you. hope the attached demo is of some use