Solved

Why doesn't Access show the decimal point?

Posted on 2004-04-21
13
378 Views
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
0
Comment
Question by:DSchat
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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


0
 

Author Comment

by:DSchat
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
FROM EE1;

Where EE1 is any table that you have

I get this

01.Mrz.2004      15,32




0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
when run in English locale, I get

01/Mar/2004      15.32

0
 

Author Comment

by:DSchat
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:nathaniel
Comment Utility
ds, have you tried setting the "decimal w/ comma type" from the Query design?

You might as well try it.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:DSchat
Comment Utility
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?
0
 
LVL 6

Expert Comment

by:nathaniel
Comment Utility
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,
0
 

Author Comment

by:DSchat
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:nathaniel
Comment Utility
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,
0
 

Author Comment

by:DSchat
Comment Utility
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.
[DBNAME]
PATCH2=15
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.
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
Comment Utility
Closed, 300 points refunded.
ee_ai_construct (.ai.)
Community Support Moderator
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

10 Experts available now in Live!

Get 1:1 Help Now