Format within Query to 2 decimal places. (1 Viewer)

tucker61

Registered User.
Local time
Today, 11:16
Joined
Jan 13, 2008
Messages
321
I have the code below in my query design view, When the query runs, the results go to 5 or 6 decimal places.

How to i format to a fixed decimal place of 2 ?

Code:
Tax_Amount: IIf((Len([Customer_Itentity])<7),[Charge_Amount]/100*20,"0.00")
 

Minty

AWF VIP
Local time
Today, 18:16
Joined
Jul 26, 2013
Messages
10,355
Format the query column - right click it get the properties up then select fixed and put 2 in the decimal places.
 

tucker61

Registered User.
Local time
Today, 11:16
Joined
Jan 13, 2008
Messages
321
Does not seem to want to play properly. Looking at the data it has been input as Charge Amount = 1.5 * 1.43 which equals 2.145. On screen it shows as 2.15, but when i export to excel it goes back to 2.145
 

isladogs

MVP / VIP
Local time
Today, 18:16
Joined
Jan 14, 2017
Messages
18,186
Correct.
The data is being saved with all decimal places but displayed with 2 d.p.
That's done for accuracy & allows you to change to more d.p. later if you want

Try using the Round function when exporting the data
Code:
Round([Charge Amount],2)
 

gippsy

Registered User.
Local time
Today, 13:16
Joined
Dec 15, 2012
Messages
39
Hello.
In a Union Query, I used Round([field],2) for every single query.
However, when I run the union query shows the value as 1080 and I need 1.080,00
I already Format the query column with 2 in the decimal places
How do I format the number to as I need?

Thanks
 

isladogs

MVP / VIP
Local time
Today, 18:16
Joined
Jan 14, 2017
Messages
18,186
Format(NumberField,"0.00")

For example Format(1080,"0.00")=1080.00
 

gippsy

Registered User.
Local time
Today, 13:16
Joined
Dec 15, 2012
Messages
39
When I typed "0.00" MS Access automatically changes to "#.000".

Fortunately, I found useful for my project the format: "Standard"
When I typed "Standard" and run the query it returns 1.080,00 and 8,00.

Cheers
 

isladogs

MVP / VIP
Local time
Today, 18:16
Joined
Jan 14, 2017
Messages
18,186
If you use Standard format, Access will automatically add commas (in the UK)
e.g. 1080 => 1,080.00 in the UK
However in your location, commas and decimal points are reversed so you get 1.080,00

Alternatively use Fixed format instead => 1080.00 (or 1080,00 in your case)
 

murray83

Games Collector
Local time
Today, 18:16
Joined
Mar 31, 2017
Messages
728
If you use Standard format, Access will automatically add commas (in the UK)
e.g. 1080 => 1,080.00 in the UK
However in your location, commas and decimal points are reversed so you get 1.080,00

Alternatively use Fixed format instead => 1080.00 (or 1080,00 in your case)

just googled for this and helped me out great deal cheers all
 

Users who are viewing this thread

Top Bottom