Access 2003 Currency Formatting

K

KC_Cheer_Coach

Is there a way to format a field in a query to currency and display it like
this:

-$4,650.00

instead of this:

($4,650.00)

?
 
F

fredg

Is there a way to format a field in a query to currency and display it like
this:

-$4,650.00

instead of this:

($4,650.00)

?
Click to expand...

-$4,650.00 is the default Currency format. If you are seeing
($4,650.00) then someone has set the Regional and Language settings to
use the parenthesis instead of the - as the negative value symbol.
Click on:
Start + Control Panel + Regional and Language Options + Regional
Options + Customize.
Change the negative number format from (1.1) to -1.1.
Any changes here will of course be reflected in ALL programs, not just
Access.

If you don't want to (or can't) change the Windows settings, then you
can display any valid format you wish.

Simply set the Format property of the column to:
$#,##.00;-$#,###.00;0.00

However, I would do it at the very end of the process, i.e. in the
form or report that will be displaying the value, not in the query.

Look up Format Property + Number and Currency datatypes.
 
J

John Spencer

A calculated field will yield a string

Format([SomeField],"#,###.00;-$#,###.00")

Or set the format property of the column or the control you are using the
value for.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
K

KC_Cheer_Coach

Thank you very much!!

fredg said:
-$4,650.00 is the default Currency format. If you are seeing
($4,650.00) then someone has set the Regional and Language settings to
use the parenthesis instead of the - as the negative value symbol.
Click on:
Start + Control Panel + Regional and Language Options + Regional
Options + Customize.
Change the negative number format from (1.1) to -1.1.
Any changes here will of course be reflected in ALL programs, not just
Access.

If you don't want to (or can't) change the Windows settings, then you
can display any valid format you wish.

Simply set the Format property of the column to:
$#,##.00;-$#,###.00;0.00

However, I would do it at the very end of the process, i.e. in the
form or report that will be displaying the value, not in the query.

Look up Format Property + Number and Currency datatypes.
Click to expand...
 
K

KC_Cheer_Coach

I checked and the lanquage settings are already set to - instead of () and
the $#,##.00;-$#,###.00;0.00 didn't make a difference. Strange. I figured
that would work for sure.
 
K

KC_Cheer_Coach

I get a syntax error when I use this line:

Format([SomeField],"#,###.00;-$#,###.00")


John Spencer said:
A calculated field will yield a string

Format([SomeField],"#,###.00;-$#,###.00")

Or set the format property of the column or the control you are using the
value for.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

KC_Cheer_Coach said:
Is there a way to format a field in a query to currency and display it like
this:

-$4,650.00

instead of this:

($4,650.00)

?
Click to expand...
Click to expand...
 
K

KC_Cheer_Coach

I did something wrong. This works great! Thanks!

John Spencer said:
A calculated field will yield a string

Format([SomeField],"#,###.00;-$#,###.00")

Or set the format property of the column or the control you are using the
value for.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

KC_Cheer_Coach said:
Is there a way to format a field in a query to currency and display it like
this:

-$4,650.00

instead of this:

($4,650.00)

?
Click to expand...
Click to expand...
 
Top