Conditional Formatting Question (1 Viewer)

[jcruzAME]

For conditional formatting on negative numbers, we're currently highlighting as red text.

The question I have is, while I know that negative currency is denoted with parenthesis, is there anyway to apply that to a standard number?

As in, is there any kind of code where I could remove the - sign and put the number in parenthesis? Or is there anyway I could remove the dollar sign from a currency format so it would show up in parenthesis?

Any info would help.

 

[Pat Hartman]

Both the Format() function and the format defined for a column in a table or query or control on a form or report use the same syntax. The following is an exerpt from Access Help:

Example of custom formats

#,###.##;(#,###.##)[Red];0,000.00;"Undefined"

This string has four custom formats and displays the following:
Positive values with two decimal places.
Negative values with two decimal places, in parentheses and red type.
Zero values as the number 0, always with two decimal places.
Null values as the word "Undefined."

Each of the four sections in the string is separated by a semicolon (.

The the following describes each section:
The number sign (#) is a placeholder for digits. If the format encounters no values, Access displays a blank space. If you want to display zeroes instead of blank spaces (for example, to display 1234 as 1234.00), you use the number 0 as the placeholder.
By default, the first section displays positive values. If you want to use a format with larger values or more decimal places, you can add more placeholders for the decimal value, such as #,###.###. As an example, 1234.5678 is displayed as 1,234.568. Notice that this format uses the comma as the thousands separator and the period as the decimal separator.

If the decimal values in the record exceed the number of placeholder characters in the custom format, Access rounds the values and displays only the number of values specified by the format. For example, if your field contains 3,456.789, but its format specifies two decimal places, Access rounds the decimal value to .79.
By default, the second section displays only negative values. If your data does not contain negative values, Access leaves the field blank. The preceding example format surrounds any negative values with literal characters — the opening and closing parentheses. It also uses the [Red] color declaration to display any negative values in red type.
By default, the third section defines the format for all zero (0) values. In this case, when the field contains a value of zero, 0,000.00 is displayed. To display text instead of a number, you can use "Zero" (surrounded by double quotation marks) instead.
By default, the fourth section defines what users see when a record contains a null value. In this case, users see the word "Undefined." You can also use other text, such as "Null" or "****". Keep in mind that surrounding characters with double quotation marks causes the format to treat those characters as literals and display them exactly as you entered them.

Note Remember that you do not need to use all four sections. For example, if your table field does accept null values, you can omit the fourth section.

So following these rules the following should work for you:
#,###.##;-#,###.##[Red];

 

[jcruzAME]

Thanks man, that was it!