Adding values in two diff columns in ms access using sql query

Question

Hey I was wondering you know how its possible to use "-" to subtract in the SELECT part of a query. So can you also use "+" to add? I've tried that and instead of adding the values together it does this 123+0.28=1230.28 does that maybe have anything to do with the number being in text format? But I hadn't ever changed format from when i used "-" and that worked . Thanks

my code :

INSERT INTO Table( Question, Calculation)

SELECT DISTINCT 'Addition' AS Question,(T2.Calculation + T1.Calculation) AS Calculation

FROM Some_Table T2, Some_Table T1

ORDER BY T2.Question;

Answer 1

It might be interpreting + as string concatenation between a and b. Try "(a - 0) + (b - 0)" to force interpretation as numbers.

Answer 2

If T2.Calculation and T1.Calculation are text data type, use the Val() function to transform them to numbers before addition.

(Val(T2.Calculation) + Val(T1.Calculation)) AS Calculation

Edit: When you use the minus operator with two text values (as in "2" - "1"), Access will transform the text values to their numerical equivalents, if possible. However, if either of the text values doesn't represent a valid number, the minus operator will give you a "Type mismatch" error ... as in "2" - "hans"

The plus operator works differently --- with two text values, it will attempt to concatenate them, same as if you'd used the concatenation operator (&) instead of the addition operator (+) ... "2" + "1" will give you "21" as a text value rather than the number 3. So, in that specific case, "2" + "1" is equivalent to "2" & "1".

An important distinction between the addition and concatenation operators is when one of the values is Null. "2" + Null yields Null. But "2" & Null yields "2".