Adding values in two diff columns in ms access using sql query
Solution 1
yes, you can use '+'
to add two numbers together.
SELECT table1.Field1, table1.Field2, Field1+field2 As SumOfFields
FROM table1;
Field1 Field2 SumOfFields
1 2 3
2 3 5
EDIT: If you have strings that you want to add together then you need to convert the fields to a number: - since it was pointed out that CLng wouldn't help the OP. It have been changed to CDbl to allow for the decimal.
SELECT table1.Field1, table1.Field2, CDbl(Field1)+CDbl(field2) As SumOfFields
FROM table1;
Solution 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".
Solution 3
It might be interpreting + as string concatenation between a and b. Try "(a - 0) + (b - 0)" to force interpretation as numbers.
Chaostryder
Chemical Engineering Coop student- Currently on a work term as a data technologist woot girls in engineering!
Updated on June 04, 2022Comments
-
Chaostryder almost 2 years
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;
-
Chaostryder almost 13 yearsThanks this is just what i needed!
-
Patrick87 almost 13 yearsIf you love it, accept it as the answer. Helps people find the best answer faster.
-
Chaostryder almost 13 yearsYep haha jsut had to wait for the few mins before u can accept answers
-
HansUp almost 13 yearsI don't think CLng is the correct function for the OP's question because
CLng("0.28")
returns 0. -
Taryn almost 13 yearsCLng was the example I used, but yes there are several different Conversion functions that can be used. if you need decimal, then CDbl or CDec. Or whatever.
-
HansUp almost 13 yearsWell ... yeah! "Or whatever" could also include CCur, CSng, even CVar. All of them will produce a real number from "0.28". CLng will not; it produces a whole number instead.
-
Taryn almost 13 years@HansUp since you are being a stickler, the code has been updated with CDbl to allow for decimal. :)
-
HansUp almost 13 years+1 I just couldn't understand why you refused to give the OP the solution she asked for. :-)
-
Chaostryder over 12 yearsi learn a lot from reading these comments LOL had to google what OP meant <--- someone who has only recently starting posting anything besides on facebook