Concatenation not addition?

  • Thread starter Thread starter Barry
  • Start date Start date
B

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
 
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
Click to expand...

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

Arvin Meyer said:
Try:

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


Barry said:
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
Click to expand...
Click to expand...
 
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


Gina Whipp said:
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

Arvin Meyer said:
Try:

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


Barry said:
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
Click to expand...
Click to expand...
Click to expand...
 
All fields are stored as numeric. One field is Double and the other Integer. I did check and even changed both to integer and got the same result.

Thanks,
Barry
 
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

Arvin Meyer said:
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


Gina Whipp said:
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

Arvin Meyer said:
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
Click to expand...
Click to expand...
Click to expand...
 
Well hush my mouth and call me crazy. CLng works. I have checked but I'm still not finding any text fields. These numbers are coming from 2 tables, tblParts and tblLabor and I am attempting to add them together to get the total. Each table has 2 fields to hold the data pertaining to the totals.

tblParts:
Qty - LongInteger
Price - Currency

tblLabor:
Hours - Double
Price - Currency

I'm not sure I understand why the CLng conversion is necessary.
Thanks,
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

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
Indeed the fields used for the calculations are unbound text fields. They are stored as numeric types in the table however, I was not aware of the fact that they would be treated as text in the unbound fields. Either Val or CLng appears to work and I have a solution and a better understanding of how this all works thanks to all your gracious help. Thanks to all for your support.
Sincerely,
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
 
Thanks to all:

I was not aware that the unbound text boxes would treat the data as text. I now have a working solution and a better understanding of this problem. Thanks again to all of you.
Sincerely,
Barry
 
He states that "both numbers are numeric"
Click to expand...

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).
 
It appears that CLng or Val both work nicely.
Click to expand...

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.
 
Back
Top