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

[Allen Browne]

Re: Conditional Forma Issue

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" <Baker.JH@Veriz on.net> wrote in message
news:7688l017td ki9rtnomrvtasbi jr5b2uufp@4ax.c om...[color=blue]
> 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[/color]

[Alan Webb]

Re: Conditional Forma Issue

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" <Baker.JH@Veriz on.net> wrote in message
news:7688l017td ki9rtnomrvtasbi jr5b2uufp@4ax.c om...[color=blue]
> 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[/color]

[John Baker]

Re: Conditional Forma Issue

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" <AllenBrowne@Se eSig.Invalid> wrote:
[color=blue]
>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.[/color]

[Allen Browne]

Re: Conditional Forma Issue

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" <Baker.JH@Veriz on.net> wrote in message
news:j1b8l0l7us 9t1g3b8ktqkn5p7 dgahv2k0o@4ax.c om...[color=blue]
> 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[/color]

[Alan Webb]

Re: Conditional Forma Issue

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" <Baker.JH@Veriz on.net> wrote in message
news:j1b8l0l7us 9t1g3b8ktqkn5p7 dgahv2k0o@4ax.c om...[color=blue]
> 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" <AllenBrowne@Se eSig.Invalid> wrote:
>[color=green]
>>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.[/color]
>[/color]