Why doesn't Access show the decimal point?

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
Who is Participating?
ee_ai_constructConnect With a Mentor Commented:
Closed, 300 points refunded.
ee_ai_construct (.ai.)
Community Support Moderator
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

DSchatAuthor Commented:
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.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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

when run in English locale, I get

01/Mar/2004      15.32

DSchatAuthor Commented:
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.
nathanielIT ConsultantCommented:
ds, have you tried setting the "decimal w/ comma type" from the Query design?

You might as well try it.
DSchatAuthor Commented:
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?
nathanielIT ConsultantCommented:
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,
DSchatAuthor Commented:
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.
nathanielIT ConsultantCommented:
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,
DSchatAuthor Commented:
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: http://www.ibm.com/software/data/db2/udb/ad

Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.