Changing Formatting of Text Values - Accounting Format

[accessnewbie352]

I am trying to change the values in a text box to show (###) if negative. For example, change -90 to (90)


Is there a way to do this - in property form maybe?

[June7]

Bing: access database negative parentheses

Review http://www.access-programmers.co.uk/...ad.php?t=89910

[accessnewbie352]

Thank you @June7 - that worked, however now something has happened with my formatting where the values in the top row do not match the values from the other rows (they are all from the same field).

I want the values to be displaced in Standard view, with no decimals.

In the property sheet, i have set:
Format : Standard
Decimal: 0

This seems to work for all of the values except the top row for some reason.

[June7]

If you are using the suggested formatting, why would it show "Standard"?

Remove .00 from the format if you don't want decimals.

I don't understand 'do not match'. By top row you mean the first record?

[Rawb]

Are you using the Format() function at all? If you use the Format() function on a numeric value, it gets converted to a text string and, as text, no longer follows any numeric formatting requirements set (such as the Format or Decimal properties of a text box)

In a situation like this - where the formatting is should be controlled by the Form, it's better to use Round(). If, for some reason, you have to use Format(), then you can convert it BACK to a numeric type with CDbl() (or CLng() since you don't want decimals to display).

Format(1.59, "#,##0") ' Displays the STRING value "2"
CDbl(Format(1.59, "#,##0")) ' Displays the NUMERIC value 2
CLng(1.59) ' Displays the NUMERIC value 2
Round(1.59, 0) ' Displays the NUMERIC value 2

[accessnewbie352]

Yes, top record. The formatting for the others are fine but the first record does not follow the formatting for some reason.

[accessnewbie352]

I'm just formatting using the Property Sheet - I'm not that familiar with codes. For the parenthesis around the values, i have using: =IIf([Pool Delta]<0,"(" & [Pool Delta] & ")",[Pool Delta])

[June7]

I used $#.00;($#.00 [Red]) in Format property and it works for all records.

Cannot replicate the issue.

[accessnewbie352]

This worked thank you but in terms of the red and the parenthesis but the first row's value still remains the same (although the text turned red).

[accessnewbie352]

Actually, upon closer inspection, it turned everything red with parenthesis, regardless of the values being positive or negative.

[June7]

Still cannot replicate the issue.

If you want to provide db, follow instructions at bottom of my post.