Avatar of SteveL13
SteveL13
Flag 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).
Microsoft Access

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
John Tsioumpris

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

Open in new window


ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

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

David Johnson, CD

@mark edwards depending upon the locale it could be #.###,## or ####,## (the command and period are switched)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Mark Edwards

According to the author's example, I'm covering the #,###.00 part of the world.  I'll let someone else cover the rest.
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:

No Decimals
Gustav Brock

Well, I don't know how you expect to format text. It is numbers (and dates) you can format.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

ASKER
Qty1TotalSell is a number field.
Mark Edwards

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.