Conditional Forma Issue

John Baker

Hi:

I have a field that i wish to use the conditional format capability on, and for some
reason it wont work.

The field is a a text box:

=[latewatchval]-[oldwatchval]

I would like to make it red when negative, and have been working with the conditional
formatting process to set it "Field Value less than" 0 (zero), but it wont trigger. I know
I am working with the right field because the "format that will be used if no conditions
are met" responds when I want to make it bold.

Can anyone offer suggestions?

Does conditional formatting work on Text Box type fields or only on numeric ones?

Best

John Baker

Nov 13 '05 #1

Allen Browne

This should work.

Any chance that Access is not understanding the data types?

For example, if LateWatchVal or OldWatchVal are calculated fields in a
query, do they right-align in the query like numbers, or left-align like
text?

You could try:
=CDbl(Nz([latewatchval], 0)) - CDbl(Nz([oldwtchval],0))
and set the Format property of this text box to:
General Number

You could also use the Debug window (press Ctrl+G) to ask Access what's
going on. Select a row where the value is less than zero, and then enter
something like this in the Debug window:
? (Forms![MyForm]![MyTextBox] < 0)
It should return True if it is less than zero.

Or ask it:
? TypeName(Forms![MyForm]![MyTextBox].Value)
to discover what type it thinks the data is.
If that is not the issue, does the status bar read "Calculating..."
seemingly for ever? There are bugs with conditional formatting (esp. with
calculated fields), so you may have triggered that. You could try moving the
calculation itself into a query, and then set the Control Source of the text
box to the name of the calculated query field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Baker" <Ba******@Verizon.net> wrote in message
news:76********************************@4ax.com...

Hi:

I have a field that i wish to use the conditional format capability on,
and for some
reason it wont work.

The field is a a text box:

=[latewatchval]-[oldwatchval]

I would like to make it red when negative, and have been working with the
conditional
formatting process to set it "Field Value less than" 0 (zero), but it wont
trigger. I know
I am working with the right field because the "format that will be used
if no conditions
are met" responds when I want to make it bold.

Can anyone offer suggestions?

Does conditional formatting work on Text Box type fields or only on
numeric ones?

Best

John Baker

Nov 13 '05 #2

Alan Webb

John,
Try a custom format string. Access & Excel both do this the same way. Look
in the help files on Format Property - Number and Currency Data Types for
more details on this. But . . . If you set the format property of your text
box to: $#,##0.00;($#,##0.00)[Red];"0";"0" then you should get what you are
looking for. I put zeros in the third and fourth positions so that zero and
null values display as 0 and lessen the chance a null value could mess up a
query that uses this column to do arithmetic.

"John Baker" <Ba******@Verizon.net> wrote in message
news:76********************************@4ax.com...

Hi:

I have a field that i wish to use the conditional format capability on,
and for some
reason it wont work.

The field is a a text box:

=[latewatchval]-[oldwatchval]

I would like to make it red when negative, and have been working with the
conditional
formatting process to set it "Field Value less than" 0 (zero), but it wont
trigger. I know
I am working with the right field because the "format that will be used
if no conditions
are met" responds when I want to make it bold.

Can anyone offer suggestions?

Does conditional formatting work on Text Box type fields or only on
numeric ones?

Best

John Baker

Nov 13 '05 #3

John Baker

Thank you.

The NZ approach worked, although I don't quite understand why the field ended up
transformed into a non numeric, since the fields in queries etc that went into it are all
numerics (defined as currency).

Thanks again

John Baker
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:

This should work.

Any chance that Access is not understanding the data types?

For example, if LateWatchVal or OldWatchVal are calculated fields in a
query, do they right-align in the query like numbers, or left-align like
text?

You could try:
=CDbl(Nz([latewatchval], 0)) - CDbl(Nz([oldwtchval],0))
and set the Format property of this text box to:
General Number

You could also use the Debug window (press Ctrl+G) to ask Access what's
going on. Select a row where the value is less than zero, and then enter
something like this in the Debug window:
? (Forms![MyForm]![MyTextBox] < 0)
It should return True if it is less than zero.

Or ask it:
? TypeName(Forms![MyForm]![MyTextBox].Value)
to discover what type it thinks the data is.
If that is not the issue, does the status bar read "Calculating..."
seemingly for ever? There are bugs with conditional formatting (esp. with
calculated fields), so you may have triggered that. You could try moving the
calculation itself into a query, and then set the Control Source of the text
box to the name of the calculated query field.

Nov 13 '05 #4

Allen Browne

Yes. JET regularly makes a mess of understanding calculated fields.

Our warnings about that are in this article:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Baker" <Ba******@Verizon.net> wrote in message
news:j1********************************@4ax.com...

Thank you.

The NZ approach worked, although I don't quite understand why the field
ended up
transformed into a non numeric, since the fields in queries etc that went
into it are all
numerics (defined as currency).

Thanks again

John Baker

Nov 13 '05 #5

Alan Webb

John,
This one trips up lots of folks. Nulls are permitted by default in many,
many database packages. Brighter lights than me can tell you why. In any
case, nulls in numeric columns are why most rdbms' have a substitution
function like Nz that will supply the missing zero when there is a null.
As to why the field ended up null instead of zero, there are a couple likely
scenarios. One, you are working with an outer join where in some cases the
rows returned have null values because that row doesn't match to a row in
the joined table. Two, your data entry people are able to skip that column
when they key a row and the front end interface is permitting nulls to be
stored instead of zeros. If the problem is the former, Nz() will fix it.
If it's the latter, then a change to the front end so it stores zero (0) as
a default value (plus maybe a dope slap to the data entry clerks) will take
care of it.

"John Baker" <Ba******@Verizon.net> wrote in message
news:j1********************************@4ax.com...

Thank you.

The NZ approach worked, although I don't quite understand why the field
ended up
transformed into a non numeric, since the fields in queries etc that went
into it are all
numerics (defined as currency).

Thanks again

John Baker
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:

This should work.

Any chance that Access is not understanding the data types?

For example, if LateWatchVal or OldWatchVal are calculated fields in a
query, do they right-align in the query like numbers, or left-align like
text?

You could try:
=CDbl(Nz([latewatchval], 0)) - CDbl(Nz([oldwtchval],0))
and set the Format property of this text box to:
General Number

You could also use the Debug window (press Ctrl+G) to ask Access what's
going on. Select a row where the value is less than zero, and then enter
something like this in the Debug window:
? (Forms![MyForm]![MyTextBox] < 0)
It should return True if it is less than zero.

Or ask it:
? TypeName(Forms![MyForm]![MyTextBox].Value)
to discover what type it thinks the data is.
If that is not the issue, does the status bar read "Calculating..."
seemingly for ever? There are bugs with conditional formatting (esp. with
calculated fields), so you may have triggered that. You could try moving
the
calculation itself into a query, and then set the Control Source of the
text
box to the name of the calculated query field.

Nov 13 '05 #6