Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-28-2017, 03:56 AM   #1
tucker61
Newly Registered User
 
Join Date: Jan 2008
Posts: 199
Thanks: 27
Thanked 2 Times in 2 Posts
tucker61 is on a distinguished road
Format within Query to 2 decimal places.

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")

tucker61 is offline   Reply With Quote
Old 07-28-2017, 04:07 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Format within Query to 2 decimal places.

Format the query column - right click it get the properties up then select fixed and put 2 in the decimal places.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
tucker61 (07-28-2017)
Old 07-28-2017, 04:55 AM   #3
tucker61
Newly Registered User
 
Join Date: Jan 2008
Posts: 199
Thanks: 27
Thanked 2 Times in 2 Posts
tucker61 is on a distinguished road
Re: Format within Query to 2 decimal places.

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

tucker61 is offline   Reply With Quote
Old 07-28-2017, 05:11 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,885
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Format within Query to 2 decimal places.

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)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
gippsy (09-28-2018), tucker61 (07-28-2017)
Old 09-28-2018, 02:54 PM   #5
gippsy
Newly Registered User
 
Join Date: Dec 2012
Posts: 39
Thanks: 12
Thanked 0 Times in 0 Posts
gippsy is on a distinguished road
Re: Format within Query to 2 decimal places.

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
gippsy is offline   Reply With Quote
Old 09-28-2018, 03:05 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,885
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Format within Query to 2 decimal places.

Format(NumberField,"0.00")

For example Format(1080,"0.00")=1080.00
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
gippsy (09-28-2018)
Old 09-28-2018, 04:20 PM   #7
gippsy
Newly Registered User
 
Join Date: Dec 2012
Posts: 39
Thanks: 12
Thanked 0 Times in 0 Posts
gippsy is on a distinguished road
Re: Format within Query to 2 decimal places.

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

gippsy is offline   Reply With Quote
Old 09-29-2018, 04:26 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,885
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Format within Query to 2 decimal places.

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)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
gippsy (09-29-2018), murray83 (09-24-2019)
Old 09-24-2019, 11:00 PM   #9
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 442
Thanks: 105
Thanked 1 Time in 1 Post
murray83 is on a distinguished road
Re: Format within Query to 2 decimal places.

Quote:
Originally Posted by isladogs View Post
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

murray83 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
format decimal places in a continuous form GKIL67 Forms 12 03-15-2012 06:43 PM
Format of decimal places rana1978 General 2 12-08-2009 09:42 PM
format this 32.2100ffffff to a number w 2 places after decimal Mandiab Queries 3 03-26-2009 05:51 AM
Textbox decimal places format Bobby1st Reports 8 11-13-2008 07:49 AM
Format Decimal Places in Code - Easy I'm sure! Elana Forms 3 09-28-2001 03:50 AM




All times are GMT -8. The time now is 09:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World