Solved Format currency in 4 decimal places (1 Viewer)

AnilBagga

Member
Local time
Today, 11:11
Joined
Apr 9, 2020
Messages
223
I have a expression in a query as below

QuoteUOM: IIf([UID]=1,[QuotePerTon]*[ConvRate]*[qrycustbagexw-new].[BagWtCorr],
IIf([UID]=2 Or [UID]=3 Or [UID]=11,[QuotePerTon]*[ConvRate],
IIf([UID]=4 Or [UID]=5 Or [UID]=6 Or [UID]=7,
[QuotePerTon]*[ConvRate]*Nz([DFL],0)*[finalgsm],[QuotePerTon]*[ConvRate]*[finalgsm])))


1. The properties section, of this expression, does not have the feature to change the format to currency. Can this be changed?

1621396151099.png


I want the value to show a currency with 4 decimal places - $8.0654

The format command using "currency" will show values with 2 decimal places and if I use the format expression with "0.0000", I will get 4 decimal place values but not the currency symbol!

What is the solution?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:41
Joined
Aug 30, 2003
Messages
36,118
Does it left align in the query? I suspect it's being seen as text, which would make it left align. You can wrap the whole expression in the CCur() function and see if that resolves the issue.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:41
Joined
May 7, 2009
Messages
19,169
you add your currency symbol to the Format property

$#,##0.0000
 

AnilBagga

Member
Local time
Today, 11:11
Joined
Apr 9, 2020
Messages
223
you add your currency symbol to the Format property

$#,##0.0000
Does it left align in the query? I suspect it's being seen as text, which would make it left align. You can wrap the whole expression in the CCur() function and see if that resolves the issue.
Ccur function should provide the value in 4 decimal places as I read. However it was providing values in currency with 2 decimal places. Strange.

However what it did was it displayed the decimal places row in Properties window as the text converted to number possibly, allowing me to change the format to 4 decimal places . Issue resolved.

Thanks
 

Cotswold

Active member
Local time
Today, 05:41
Joined
Dec 31, 2020
Messages
521
In the past I have found that built-in functions like Currency aren't always reliable. I believe that Currency types are only masks and that 16 places are hanging on in the background.
For that reason I will only use Integer Calculations (long actually) and then string handle the decimal point to show correctly in the result on the fly.
All line totals and calculation results in my databases are always Longs. If you add up any number of Longs you will always have the same result.

If at sometime in the future an issue arises, then you can exclude any of your calculations from the start.
 

Users who are viewing this thread

Top Bottom