Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

How display query results as currency with 2 decimals?

I have a query that no matter how hard I try will not display the results in currency format.  I want the values to display in currency format with 2 decimals.  In fact when I look at the properties for the column I see currency as a choice but there is no place to enter 2 decimals.  For example, I see "11155.3" (without the quote marks).
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Do you mean something like this
Format([YourValue],"$#.00") 

Open in new window


ASKER CERTIFIED SOLUTION
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
there is no place to enter 2 decimals
That can't be true. A numeric field always has that option. Would like to see a screenshot without the Decimals option.

But you can always convert to Currency and it will display by default as Currency:

Select 
    *, 
    CCur([Amount]) As CurrencyAmount, 
    CCur([Amount] * 0.1) As CurrencyDiscount
From
    YourTable

Open in new window

@mark edwards depending upon the locale it could be #.###,## or ####,## (the command and period are switched)
According to the author's example, I'm covering the #,###.00 part of the world.  I'll let someone else cover the rest.
Avatar of SteveL13

ASKER

I ended up getting it with:

Buy Price 1: Format(IIf([Qty1Select]="X",[Qty1TotalSell],""),"Currency")

Here is the screen shot showing no place to enter the decimals:

User generated image
Well, I don't know how you expect to format text. It is numbers (and dates) you can format.
Qty1TotalSell is a number field.
OK.  This makes no sense:
Format(IIf([Qty1Select]="X",[Qty1TotalSell],""),"Currency")

because it says:  "If [Qty1Select] equals the text character "X", then the value is the number in [Qty1TotalSell], otherwise, use an empty string as the number.  Then take the result and format it as "Currency".

"Currency" is a format that only works on numbers, so what do you get if you have an empty string?
I think you meant use a zero (0).  The currency format should format your numbers as $#,###.00 if you are familiar with formatting symbology.