You can use the Format function.

UK: Format([myCurrency],"'#,###.00;('#,###.00)")
US: Format([myCurrency],"$#,###.00;($#,###.00)")

Currency Symbols

Hi Jean-Pierre,

In Access, the entire column has to have the same format.  It's not like in Excel where you can format individual cells.  To do that in Access, usually you can use conditional formatting, but it won't work here because you can't set currency formats with conditional formatting.

You need to make a query that sets the currency format of every row conditionally (using the IIf() or Switch() function) based on the currency type of that row.  See the example in the picture below, where  I use the Switch() to decide whether to put the $, ', ', or ' symbol in front of the amount, based on the CurrencyType field.  This site has a video on the Switch() function if you are unfamiliar with it.

Yes, this method is a little cumbersome.  But you only have two currency formats, so it should be simpler for you.

I said in my previous post that a column can only show one format, and it is still true in my example above.  Even though the result shows different currency formats in a column, they all have the same format: literal *text* values, not numbers with currency formats.  So they cannot be used in calculations.  To use them as numbers, they need to be converted back to numbers, with the Val() function, for instance.

Good morning all how about some creative thinking add a field with USD  or $ query by that this way you can still use the format and keep its value?

☝ Oh Yeah!!! ☝