We help IT Professionals succeed at work.

How display query results as currency with 2 decimals?

SteveL13
SteveL13 asked
on
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).
Comment
Watch Question

View Solution Only

John TsioumprisSoftware & Systems Engineer
BRONZE EXPERT
Distinguished Expert 2019

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

Chief Technology Officer
BRONZE EXPERT
Commented:
Be careful what you ask for:

Format([ValueFieldName],'Currency') displays as $1,111.10
Format([ValueFieldName],'$#.00') displays as $1111.10
Format([ValueFieldName],'#.00') displays as 1111.10
Format([ValueFieldName],'Fixed') displays as 1111.10
Format([ValueFieldName],'Standard') displays as 1,111.10
Format([ValueFieldName],'#,###.00') displays as 1,111.10

Pick the one you want...
Gustav BrockMVP
SILVER EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
David Johnson, CDSimple Geek from the '70s
GOLD EXPERT
Distinguished Expert 2019

Commented:
@mark edwards depending upon the locale it could be #.###,## or ####,## (the command and period are switched)
Mark EdwardsChief Technology Officer
BRONZE EXPERT

Commented:
According to the author's example, I'm covering the #,###.00 part of the world.  I'll let someone else cover the rest.
SteveL13

Author

Commented:
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:

No Decimals
Gustav BrockMVP
SILVER EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, I don't know how you expect to format text. It is numbers (and dates) you can format.
SteveL13

Author

Commented:
Qty1TotalSell is a number field.
Mark EdwardsChief Technology Officer
BRONZE EXPERT

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.