Prev: Extracting part of a text value
Next: Query to create new table format
From: KC_Cheer_Coach on 23 Jun 2008 12:00
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)

?
From: fredg on 23 Jun 2008 13:36
On Mon, 23 Jun 2008 09:00:02 -0700, KC_Cheer_Coach wrote:

> 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)
>
> ?

-$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.




--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
From: John Spencer on 23 Jun 2008 13:40
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 wrote:
> 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)
>
> ?
From: KC_Cheer_Coach on 23 Jun 2008 14:21
Thank you very much!!

"fredg" wrote:

> On Mon, 23 Jun 2008 09:00:02 -0700, KC_Cheer_Coach wrote:
>
> > 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)
> >
> > ?
>
> -$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.
>
>
>
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>
From: KC_Cheer_Coach on 23 Jun 2008 14:37
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.

"fredg" wrote:

> On Mon, 23 Jun 2008 09:00:02 -0700, KC_Cheer_Coach wrote:
>
> > 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)
> >
> > ?
>
> -$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.
>
>
>
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>
 |  Next  |  Last
Pages: 1 2
Prev: Extracting part of a text value
Next: Query to create new table format