Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 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