Go Premium for a chance to win a PS4. Enter to Win


Why doesn't Access show the decimal point?

Posted on 2004-04-21
Medium Priority
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
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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: http://www.ibm.com/software/data/db2/udb/ad

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.
Suggested Courses

971 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