My Sql query, shows the wrong sum from my database

[arneweise77]

Hi!

I´ve written a sql query, trying to get the sum of several columns in my access database.
The column which holds figures, has the format text. I know I maybe could change this and have it as Integer instead, but is there another way to get past this problem? The whole database is in text which I don´t prefer to change.

Just using Sum in the query makes the commas dissapear.
For example, 1.0 + 2.0 gives the sum 30!

Here´s the code:
SQL = "SELECT SUM(hours) " & _
"FROM MP62, MP50 " & _
"WHERE MP62.workcenter = MP50.workcenter_code AND" & _
" MP50.workcenter_code = 'K'"

If anyone knows how to solve this, please help me.
Thanx!

 

[jfrost10]

arne...you have to let go. I know, I know...the data has always been text. But its time to let them grow, and become the integers they truly are.

all kidding aside, you won't be able to sum text fields the way you want; they have to be in some numerial format (int, long, double, etc.)

Otherwise, you get wanky things happening. Besides, keeping all your data as text is a bad idea: it doesn't truly represent the data you're storing, and it makes working with the data in code a nightmare.

Jack