Concatenation not addition?

[Barry]

Good evening all:
I am trying to add numbers together and they are being concatenated rather
than summed. Both numbers are numeric.
Example:
Field1: 5
Field2: 10
Field1 + Field2 = 510

Does anyone have an explanation for this behavior?
Thanks,
Barry

 

[Arvin Meyer [MVP]]

Try:

= Sum([Field1]+[Field2])

 

[Gina Whipp]

Barry,

Are the fields being stored as numbers or text? What you are getting
suggests text. Either change the fields to numeric or...

Val([Field1]) + Val([Field2])

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

Good evening all:
I am trying to add numbers together and they are being concatenated rather
than summed. Both numbers are numeric.
Example:
Field1: 5
Field2: 10
Field1 + Field2 = 510

Does anyone have an explanation for this behavior?
Thanks,
Barry

This will happen if the values are Text. If these are unbound textboxes on a
form, they'll be treated as text (if they are fields in a table, they
shouldn't unless of course they ARE of Text datatype; note that a Text field
can contain numbers!)

You might try

= Val([field1]) + Val([field2])

as the control source of a textbox on a form, or

TheSum: Val([field1]) + Val([field2])

as a calculated field in a query.

 

[Gina Whipp]

Arvin,

I could be wrong but I don't think that will work on text fields and the
only time Access doesn't add is if they are text fields, it concates (is
that a word??? okay well, you know what I mean).

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Try:

= Sum([Field1]+[Field2])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Good evening all:
I am trying to add numbers together and they are being concatenated
rather
than summed. Both numbers are numeric.
Example:
Field1: 5
Field2: 10
Field1 + Field2 = 510

Does anyone have an explanation for this behavior?
Thanks,
Barry

 

[Arvin Meyer [MVP]]

Hi Gina,

He states that "both numbers are numeric"

You are correct. He will get an error if they aren't, but I'm giving him the
benefit of the doubt until he gets the error.

BTW, the word you were looking for is concatenate.
--
Arvin

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

I could be wrong but I don't think that will work on text fields and the
only time Access doesn't add is if they are text fields, it concates (is
that a word??? okay well, you know what I mean).

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Try:

= Sum([Field1]+[Field2])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Good evening all:
I am trying to add numbers together and they are being concatenated
rather
than summed. Both numbers are numeric.
Example:
Field1: 5
Field2: 10
Field1 + Field2 = 510

Does anyone have an explanation for this behavior?
Thanks,
Barry

 

[KARL DEWEY]

I bet your fields are text.

Try this --
CLng([Field1]) + CLng([Field2])

 

[kc-mass]

Try:
CLng(Field1) + CLng(Field2)

Regards

Kevin

 

[Gina Whipp]

Arvin,

True he does state that AND he is correct, numbers are numeric but fields
are not <g> and I find people often get that confused. They think storing a
number in a text field *makes* the field numeric.

<Oh Dear> Well, I do have the David's typist sister, I see she isn't much
better at spelling! Thanks!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina,

He states that "both numbers are numeric"

You are correct. He will get an error if they aren't, but I'm giving him
the benefit of the doubt until he gets the error.

BTW, the word you were looking for is concatenate.
--
Arvin

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

I could be wrong but I don't think that will work on text fields and the
only time Access doesn't add is if they are text fields, it concates (is
that a word??? okay well, you know what I mean).

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

Try:

= Sum([Field1]+[Field2])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Good evening all:
I am trying to add numbers together and they are being concatenated
rather
than summed. Both numbers are numeric.
Example:
Field1: 5
Field2: 10
Field1 + Field2 = 510

Does anyone have an explanation for this behavior?
Thanks,
Barry

 

[Barry]

John:
Yes indeed they are unbound fields. I am using them to sum things up and
give a visual of prices, payments, amounts due etc. I was not aware that the
fields were treated as such. Thanks to all for the help.
Sincerely,
Barry

Good evening all:
I am trying to add numbers together and they are being concatenated rather
than summed. Both numbers are numeric.
Example:
Field1: 5
Field2: 10
Field1 + Field2 = 510

Does anyone have an explanation for this behavior?
Thanks,
Barry

This will happen if the values are Text. If these are unbound textboxes on a
form, they'll be treated as text (if they are fields in a table, they
shouldn't unless of course they ARE of Text datatype; note that a Text field
can contain numbers!)

You might try

= Val([field1]) + Val([field2])

as the control source of a textbox on a form, or

TheSum: Val([field1]) + Val([field2])

as a calculated field in a query.

 

[Barry]

Thanks,
It appears that CLng or Val both work nicely. I was unaware that the
unbound text boxes would be treated as text.
Thanks for the help.
Barry

 

[David W. Fenton]

He states that "both numbers are numeric"

If they were numeric, they wouldn't have concatenated with the +
operator (which is the concatenation operator that propagates Null
*if* it's used with non-numeric data).

 

[David W. Fenton]

I bet your fields are text.

I would bet that, too.

Try this --
CLng([Field1]) + CLng([Field2])

Well, that would strip any decimal parts, so not the best choice,
which is:

Val([Field1]) + Val([Field2])

 

[David W. Fenton]

It appears that CLng or Val both work nicely.

Use Val() and not CLng(), because CLng() will return only the
Integer part of the values. When all you want to do is coerce
strings into numbers, Val() is the best way to do it. CLng() coerces
a string to a valid long integer value (i.e., no decimal part),
which means it does more than just convert the string to a numeric
value.

 

[KARL DEWEY]

I considered other than CLng but the examples posted were all integers.