Solved

Access query does not show decimal comma

Posted on 2014-04-30
7
2,581 Views
Last Modified: 2014-05-05
When I query fields in the database with currency information (numeric, 2 decimals), the query results shows all the fileds without decimal commas.
If the query field result, the values in the db like
21671,00 and 23134,18 become 21671 and 2313418

How can I solve the problem of missing decimal information? when the information in the db as decimals value different from 0, this is not shown and 23.134,18 becomes 2.313.418.

Thanks
query-problem.jpg
0
Comment
Question by:Gianni
  • 3
  • 3
7 Comments
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 40031765
Set the Format property of the field in the query to: 0.00 or 0,00

/gustav
0
 

Author Comment

by:Gianni
ID: 40031809
Where I should set the settings suggested? In which field of the attached image?

Thanks, Gianni
format-property-query.jpg
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40031843
In the property Formato of Cost_Purchase.

/gustav
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40031909
select "Standard" in the Formato property
0
 

Accepted Solution

by:
Gianni earned 0 total points
ID: 40032089
The problem was on the settings of the Oracle ODBC Connection string used by MS Access.
The 'NUM' parameter in the ODBC conection string was 'NLS', that I've changet with 'MS'.

After the change on the odbc settings, that you can perform also in the NumericSetting entry of MS registry settings (regedit), I've:
1 rebboted the machine
2 reconnected the database table in the access db

After that, the decimal field was displayed correctly.
You can optimize the format of the field using Set the Format property of the field in the query.


The possible options for the Oracle NUM Connection Parameter field are:
NLS=Use Oracle NLS numeric settings (to determine the decimal and group separator).
MS=Use Microsoft regional settings.
US=Use US settings

The full list of Oracle connection parameter could be found at: https://community.oracle.com/thread/1056197?tstart=0

Gianni
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40032105
OK, how could we know.

/gustav
0
 

Author Closing Comment

by:Gianni
ID: 40041640
I was using Access to connect to an Oracle database using ODBC.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question