Solved

Access query does not show decimal comma

Posted on 2014-04-30
7
2,323 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 49

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 49

Expert Comment

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

/gustav
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 119

Expert Comment

by:Rey Obrero
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 49

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now