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;

@Paul Sonier Thanks! ive not yet gotten very used to making the code look nice lol — Chaostryder
– Chaostryder, Commented Jul 22, 2011 at 22:30
Please include the table structure for Some_table. But sure, you can add and subtract numbers. It would turn into a string if it started that way. — Ariel
– Ariel, Commented Jul 22, 2011 at 22:31
- only makes sense as a numeric subtraction, so the values get converted to numbers, but + is ambiguous, it could be addition or concatenation, so it depends on the types of the values. — Neil
– Neil, Commented Jul 22, 2011 at 22:44
Yes, but in general, my experience is that the + concatenation operator will coerce strings to numbers, so this result is not expected to me. — David-W-Fenton
– David-W-Fenton, Commented Jul 24, 2011 at 21:26

Patrick87 · Accepted Answer · 2011-07-22 22:33:02Z

4

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

answered Jul 22, 2011 at 22:33

Patrick87

28.4k3 gold badges41 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick87 Over a year ago

If you love it, accept it as the answer. Helps people find the best answer faster.

Chaostryder Over a year ago

Yep haha jsut had to wait for the few mins before u can accept answers

HansUp · Accepted Answer · 2011-07-23 00:35:49Z

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".

Taryn · Accepted Answer · 2011-07-23 01:01:50Z

3

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;

edited Jul 23, 2011 at 1:01

answered Jul 22, 2011 at 22:31

Taryn

249k57 gold badges375 silver badges409 bronze badges

6 Comments

HansUp Over a year ago

I don't think CLng is the correct function for the OP's question because CLng("0.28") returns 0.

Taryn Over a year ago

CLng 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 Over a year ago

Well ... 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 Over a year ago

@HansUp since you are being a stickler, the code has been updated with CDbl to allow for decimal. :)

HansUp Over a year ago

+1 I just couldn't understand why you refused to give the OP the solution she asked for. :-)

|

Artjom B. · Accepted Answer · 2014-12-04 19:53:38Z

-1

Just precede your formula with 0+ and it will know you're talking in numbers instead of strings:

Instead of [A]+[B]+[C] put 0+[A]+[B]+[C]

edited Dec 4, 2014 at 19:53

Artjom B.

62k26 gold badges137 silver badges236 bronze badges

answered Dec 4, 2014 at 18:37

Chess Master Ryan

1

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related