Why doesn't Access show the decimal point?

Posted on 2004-04-21
Last Modified: 2007-12-19
I'm try to look at DB2 data on a Unix server over ODBC (db2 driver) with MS Access. I've got a couple of fields that have data that looks like 10,000. I'm in Germany and they use a comma for the decimal point here. When I look at the same field in Access it shows me 10000 without the decimal point. I went over DB2 tools and saw that the data in db2 is shown correctly there as well as over ODBC to another query tool (WinSQL). The data was shown correctly there as well.
Do I need to set something in Access?

Thanks for the help,
Douglas Schatvet
Question by:DSchat
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
LVL 65

Expert Comment

ID: 10877352
could be two things

1. Access does use regional settings, have you checked this in control panel, check in numbers/currency tab

2. Any formatting of data done in Access (how r u viewing this value), there may be a format mask applied


Author Comment

ID: 10877449
1. The regional settings are set properly to germany and with a comma instead of a decimal point.

2. I just look at the complete table as a shortcut/link. (I'm not sure what Access calls it) Anyways there should be no format mask that I have set up.

I just tried a Pass through Query directly to two problem columns. The data comes back here incorrectly also.
LVL 65

Expert Comment

ID: 10877748
hmm, I just changed my regional setting to german and Im getting a comma instead of a decimal point, so its working

Im even getting dates in german (I think its german, its certainly not english!)

try this query, what do you get

SELECT TOP 1 Format('01/03/2004','dd/mmm/yyyy') , 15.32

Where EE1 is any table that you have

I get this

01.Mrz.2004      15,32

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 65

Expert Comment

ID: 10877821
when run in English locale, I get

01/Mar/2004      15.32


Author Comment

ID: 10878330
OK I tried that and got the same results. I had to first create a local table, DB 2 doesn't understand TOP. So I would say my regional settings are ok. One thing that I find also strange, is that when I created the table (two columns - TEXT and Number- with fieldsize "Long Integer" with auto decimal setting)  I typed in values such as WWW 10,00 or eee 12.34. In both cases Access chopped off everything behind comma or decimal point. I just changed the decimal setting to 4. After I did that I'm now getting similar results with my local table.

I set to England english and type in 10.004. Access then set the data to 10. When I type in 10,004 it then displays 10004.

I set it back to german type in 10.004 and get back 10004 and with a comma it shows 10 when i type in 10,004.
What appears to be happening is the digits after the comma (or decimal point in english) are just being cut off.

Expert Comment

ID: 10886242
ds, have you tried setting the "decimal w/ comma type" from the Query design?

You might as well try it.

Author Comment

ID: 10886717
I'm sorry, but I'm not sure what you mean. I haven't designed a query for the table I just have a link to the table on a server and just try to open the table.

Where would I put that in Query design?

Expert Comment

ID: 10887001
by the way,  I assume that the tables are linked (via odbc, as you explained), so we can't do about changing the format type in the table properties.

so another option is to create a query that would change the view formatting of this number.
a query is a tool in access to view selected fields from a table (you can also set criteria to filter viewing of record)

here's a brief procedure in creating query:

1. click on the Query tab (from Access database window, along with the Table, Report, Forms, etc.)

2. create new Query (click on the "new" button) then choose design view

3. when the new design query opens (select the table you want to query from the "Show table" selector box (click the Add button to put the table into the design window, then close).

4. from the design query window (the table you selected should be there), select the fields from the table and drag into the row (or click on the field row to manually select a field to display)

5. now put your cursor to the field you want to set the view property then click on the View->Properties menu (a small property box will be displayed), click on the format, then select the "standard" type

then try running the query...

hope that works,

Author Comment

ID: 10887606
ok, I think we're getting closer. I've two columns with these floating point values. When I look at them with DB2 tools, I see values like

10,000 -1,000  where by the comma is the decimal point. After I defined the query as you suggested, the return values were then 10.000,000  -1.000,00

I then tried the other format settings, but nothing came back as I would prefer.

Expert Comment

ID: 10887715
OK now were going to set some parameters on the regional settings or options:

if your setting is Germany (germany) then:
the Number setting should be:
  decimal symbol = . (period)
  digit grouping symbol = , (comma)

or the simpliest, try the "trial and error" method.
while the query output is active, open the regional setting then experiment on the parameters. Clicking the apply button everytime you set one parameter will change the format of the active opened query.

hope that helps,

Author Comment

ID: 11358824
Hi to everyone who helped me here. I was unfortunately only able to get the correction from IBM support in the end though. The correction for this problem is an entry into the DB2CLI.INI file.

You have to put PATCH2=15 in the file in the section for the corresponding database.
This is IBM explaination of this patch:
Causes a period separator to be used instead of the default locale's decimal separator in character output.

An explaination to all the available patches for DB2 ODBC is under:

Thanks again.

Accepted Solution

ee_ai_construct earned 0 total points
ID: 11574654
Closed, 300 points refunded.
ee_ai_construct (.ai.)
Community Support Moderator

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

627 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