Displaying negatives values in Parentheses

[smichaels1234]

I want to have my negative numbers, in standard display in parenthesis rather than with the dash in front in my reports. How can I go about doing this. Here's an example:

-3
(3)

 

[KenHigg]

I think if you set the format to something like the following it work:

0;(0)

???

 

[smichaels1234]

Yeah, that's not what I am trying to do. I have a report that generates numbers. I want to set the format property to standard. Is there a property of some sort that I am not seeing? I'm sorry it is a formula.

 

[KenHigg]

So you have it in a text box on the report?

 

[smichaels1234]

Yes, that's correct. Here's the formula:

=([On_Hand]-[Qty_Needed])

 

[KenHigg]

=format(([On_Hand]-[Qty_Needed]),"0;(0)")

???

 

[smichaels1234]

That worked but it took away the standard format. I need that format so my commas are all there. Also, can I make this (0) red?

 

[KenHigg]

#,###;(#,###)[Red]


???

 

[KenHigg]

Sorry:

=format(([On_Hand]-[Qty_Needed]),"#,###;(#,###)[Red]")

???

 

[KenHigg]

I'll get it correct in a minute:

=format(([On_Hand]-[Qty_Needed]),"#,##0;(#,##0)[Red]")

 

[voidcranium]

Might look here
http://www.techonthenet.com/access/functions/numeric/format.php

For example:
Format (210.6, "#,##0.00") would return '210.60'
Format (210.6, "Standard") would return '210.60'
Format (0.981, "Percent") would return '98.10%'
Format (1267.5, "Currency") would return '$1,267.50'

VBA Code
The Format function can be used in VBA code. For example:
Dim LValue As String
LValue = Format (0.981, "Percent")
In this example, the variable called LValue would now contain the value of '98.10%'.

 

[smichaels1234]

Voidcranium,
I don't understand where in my code I would put Standard. Here is my code again:

=format(([On_Hand]-[Qty_Needed]),"#,##0;(#,##0)[Red]")

I am not looking for a specific number.

 

[smichaels1234]

Actually, KenHigg fixed my problem but the only thing that didn't work was the red. Nice work. Thanks.

 

[voidcranium]

After reading this and looking into it myself I found this help file in access.
'Format Property — Number and Currency Data Types'
It helped me understand where Ken got his answer.

Just a shot in the dark but heres my try.

=format(([On_Hand]-[Qty_Needed]),"#,##0[Black];(#,##0)[Red];0;0")

 

[smichaels1234]

For some reason the colors are just not working. Not sure how to do this.

 

[Rich]

Put this part in the display format on the property sheet for the textbox
#,##0.00[Black];#,##0.00[Red]
Scrap the format function and just put =[On_Hand]-[Qty_Needed] as the control source for the textbox

 

[smichaels1234]

Thank you so much. That was very simple. The things I guess you learn along the way huh?