Formatting Percentage Fields

G

Guest

Hi

I have a field on a Continuous form and I want to show the text colour in
red if the content is a negative value. I have set the Conditional Formatting
fields to show -

Field Value Is .. less than .. 0

This works fine for normal values but if I change the field Format property
to Percent (which is what I need) the field always shows in black. Is there
any way to show negative percentages in red.
 
A

Allen Browne

Peter, I just tried that and it worked fine here, so we need to track down
what the difference is.

Is this field bound to a table? If so, when you open the table in design
view, what is the Data Type of this field? Is it Number or Text? And what is
the Field Size property (lower pane in table design)?

What you are doing should work if the text box is bound to a Number type
field. Typically the size would be Double for a fractional value like a
percentage.
 
G

Guest

Hi Allen

Is it a bound field? Yes and No I suppose. The field ControlSource property
has this in it -
=([PresentValue]-[LastValue])/[LastValue]
where the fields PresentValue and LastValue are currency fields in the same
record.
Any ideas on why this shouldn't work.

Hi Tom,
Yes, using the format function does work, i.e. :-
#,##0.00%;-#,##0.00%[Red];#,##0.00%;#,##0.00%
The slight problem with this, I think, is that I can only change the text
colour so if my client wants to change the BackColor I would need Conditional
Formatting or revert to some VBA code. Hopefully, however, he will be happy
with this.

Thanks again guys, if you have any other ideas I would still be interested
to hear them.
 
G

Guest

Hi Peter,

You can consider the text box to be a bound control. It is bound to the
expression that you entered into the control source.

I just tried to duplicate your findings using the two UnitPrice fields in
the Northwind sample database. I am able to get negative values to display in
red, using conditional formatting. You might try adding another text box,
temporarily, with this control source:

=IsNumeric(([LastValue]-[PresentValue])/[PresentValue])

A value of -1 (True) means that the expression returns a number. A value of
0 (False) means that something is wrong.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Peter Hibbs said:
Hi Allen

Is it a bound field? Yes and No I suppose. The field ControlSource property
has this in it -
=([PresentValue]-[LastValue])/[LastValue]
where the fields PresentValue and LastValue are currency fields in the same
record.
Any ideas on why this shouldn't work.

Hi Tom,
Yes, using the format function does work, i.e. :-
#,##0.00%;-#,##0.00%[Red];#,##0.00%;#,##0.00%
The slight problem with this, I think, is that I can only change the text
colour so if my client wants to change the BackColor I would need Conditional
Formatting or revert to some VBA code. Hopefully, however, he will be happy
with this.

Thanks again guys, if you have any other ideas I would still be interested
to hear them.

--
Peter Hibbs


Allen Browne said:
Peter, I just tried that and it worked fine here, so we need to track down
what the difference is.

Is this field bound to a table? If so, when you open the table in design
view, what is the Data Type of this field? Is it Number or Text? And what is
the Field Size property (lower pane in table design)?

What you are doing should work if the text box is bound to a Number type
field. Typically the size would be Double for a fractional value like a
percentage.
 
A

Allen Browne

Try typecasting the result to force it to be recognised as a numeric value:
=CDbl(Nz(([PresentValue]-[LastValue])/[LastValue], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Peter Hibbs said:
Hi Allen

Is it a bound field? Yes and No I suppose. The field ControlSource
property
has this in it -
=([PresentValue]-[LastValue])/[LastValue]
where the fields PresentValue and LastValue are currency fields in the
same
record.
Any ideas on why this shouldn't work.

Hi Tom,
Yes, using the format function does work, i.e. :-
#,##0.00%;-#,##0.00%[Red];#,##0.00%;#,##0.00%
The slight problem with this, I think, is that I can only change the text
colour so if my client wants to change the BackColor I would need
Conditional
Formatting or revert to some VBA code. Hopefully, however, he will be
happy
with this.

Thanks again guys, if you have any other ideas I would still be interested
to hear them.

--
Peter Hibbs


Allen Browne said:
Peter, I just tried that and it worked fine here, so we need to track
down
what the difference is.

Is this field bound to a table? If so, when you open the table in design
view, what is the Data Type of this field? Is it Number or Text? And what
is
the Field Size property (lower pane in table design)?

What you are doing should work if the text box is bound to a Number type
field. Typically the size would be Double for a fractional value like a
percentage.
 
G

Guest

Hi Allen

Yes that works fine. Something else I'm going to have to remember.

Thanks again.
--
Peter Hibbs


Allen Browne said:
Try typecasting the result to force it to be recognised as a numeric value:
=CDbl(Nz(([PresentValue]-[LastValue])/[LastValue], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Peter Hibbs said:
Hi Allen

Is it a bound field? Yes and No I suppose. The field ControlSource
property
has this in it -
=([PresentValue]-[LastValue])/[LastValue]
where the fields PresentValue and LastValue are currency fields in the
same
record.
Any ideas on why this shouldn't work.

Hi Tom,
Yes, using the format function does work, i.e. :-
#,##0.00%;-#,##0.00%[Red];#,##0.00%;#,##0.00%
The slight problem with this, I think, is that I can only change the text
colour so if my client wants to change the BackColor I would need
Conditional
Formatting or revert to some VBA code. Hopefully, however, he will be
happy
with this.

Thanks again guys, if you have any other ideas I would still be interested
to hear them.

--
Peter Hibbs


Allen Browne said:
Peter, I just tried that and it worked fine here, so we need to track
down
what the difference is.

Is this field bound to a table? If so, when you open the table in design
view, what is the Data Type of this field? Is it Number or Text? And what
is
the Field Size property (lower pane in table design)?

What you are doing should work if the text box is bound to a Number type
field. Typically the size would be Double for a fractional value like a
percentage.


I have a field on a Continuous form and I want to show the text colour
in
red if the content is a negative value. I have set the Conditional
Formatting
fields to show -

Field Value Is .. less than .. 0

This works fine for normal values but if I change the field Format
property
to Percent (which is what I need) the field always shows in black. Is
there
any way to show negative percentages in red.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top