I wrote a workaround for this Issue:

www.mechtilde.de/mysql2ooo/summenproblem.html

I try it as the intern db (HSQL) as MySQL 4.1 via ODBC.3.51.09 (Debain Sarge)

I've done a wrong link:
http://www.mechtilde.de/mysql2ooo20/summenproblem.html

Created attachment 32957 [details]
BeispielDB in MySQL

add to CC

I tested it also with RC1 of 2.0.2

and the sum is wrong

see also http://www.mechtilde.de/mysql2ooo20/summenproblem.html

there is a german description and screenshots which show the right sum with HSQL
and the wrong sum with a MySQL-DB

reassigned

Hi,

I can't reproduce this.

I have used:
locale=de_DE.UTF-8
myodbc.3.51.11
OOo 2.0.2

The query return the right number with decimal places.
The 'Betrag' field is an float field.(may be it's important)

Can you please check this again with the current myodbc driver 3.51.11.

I set this issue now to WORKSFORME.

Bye Marc

Now it works also for me in m162
with odebc 3.51.09

Thanks Marc

so the issue can be closed

So I close this issue now.

Bye Marc

If you use an English locale it works right

There is something wrong with the localisation and the ODBC-Driver and MySQL.

Now I use m186

I reopen this issue.

there are some changes with the problem.

The query shows the right values with the right decimals.

But:

If I choose F4 in Calc and want to transfer the query to the table. I can't
calculate with this values.

A right chlick on the top of the column showsthat it is formatted as "text".

What can I do to get currency values to calculate with.

some thing has changed here:

to get the wright values in calc via F4 you can't use the workaround posted before.
First you have _to mark_ the colums you want to format.

Now you can use the following macro:

sub number2number
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "NumberFormatValue"
args1(0).Value = 10108

dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(17) as new com.sun.star.beans.PropertyValue
args2(0).Name = "SearchItem.StyleFamily"
args2(0).Value = 2
args2(1).Name = "SearchItem.CellType"
args2(1).Value = 0
args2(2).Name = "SearchItem.RowDirection"
args2(2).Value = true
args2(3).Name = "SearchItem.AllTables"
args2(3).Value = false
args2(4).Name = "SearchItem.Backward"
args2(4).Value = false
args2(5).Name = "SearchItem.Pattern"
args2(5).Value = false
args2(6).Name = "SearchItem.Content"
args2(6).Value = false
args2(7).Name = "SearchItem.AsianOptions"
args2(7).Value = false
args2(8).Name = "SearchItem.AlgorithmType"
args2(8).Value = 1
args2(9).Name = "SearchItem.SearchFlags"
args2(9).Value = 71680
args2(10).Name = "SearchItem.SearchString"
args2(10).Value = "."
args2(11).Name = "SearchItem.ReplaceString"
args2(11).Value = "&"
args2(12).Name = "SearchItem.Locale"
args2(12).Value = 255
args2(13).Name = "SearchItem.ChangedChars"
args2(13).Value = 2
args2(14).Name = "SearchItem.DeletedChars"
args2(14).Value = 2
args2(15).Name = "SearchItem.InsertedChars"
args2(15).Value = 2
args2(16).Name = "SearchItem.TransliterateFlags"
args2(16).Value = 1280
args2(17).Name = "SearchItem.Command"
args2(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args2())

end sub

now (Version 2.3.1) it works for me with sums

-> closed