How do I display a calculated negative number with a minus sign?

[tetsuo2030]

Greetings all:

I have a query like:

"Select myTable.WasCost
   , myTable.IsCost
   , [WasCost]-[IsCost] 
      AS myDifference 
FROM myTable;"

WasCost and IsCost are both set to Currency in myTable. If the myDifference comes out negative, it looks like ($100.00). User requests that it display as -$100.00 instead.

I believe one can change the currency formatting in the table, but the Microsoft article on Formatting says the stored value for negative numbers is -$X.XX andthe display is ($X.XX). Also, since this is a calculated field, if I could make it work in the tables, would the query translate accordingly?

[zmbd]

Specify how data displays by using custom formatting (ACC2007/2010)
Has all of the details.

Also please use the [CODE/] format for all SQL/Code/Formatted text. (^-^)

[ADezii]

Try the following to force the requested Format:

SELECT myTable.WasCost, myTable.IsCost, 
Format([WasCost]-[IsCost],"$#,###.00") AS myDifference
FROM myTable;