Problem with MS Access + Operator in Query

[Andysgriff]

Trying to get a simple query to ADD 4 values in a query. I am using the
expression builder and all the other BASIC Operators work, i.e *, -, /.

E.g 4 fields in the query, values 2,4,6,8. When I use the * Operator I get
384 as the returned value, no problem there.

Expr1: [SPIR_Parts_tbl].[CsI]*[PrI]*[PenI]*[LtI]

But when I use the + Operator I get 2468 as the returned value

 

[Stefan Hoffmann]

hi Andy,

Expr1: [SPIR_Parts_tbl].[CsI]*[PrI]*[PenI]*[LtI]
But when I use the + Operator I get 2468 as the returned value

This can only happen if one(all) of your involved fields is a string
(Text).

"2" * "4" * "6" * "8"

* is an arithemtic operator. All values can be converted to a number, so
you get your result.

"2" + "4" + "6" + "8"

+ is a string (Text) operator, too. As all values are text its
concatenates the strings.

CLng("2") + CLng("4") + CLng("6") + CLng("8")

will return your intended sum.


mfG
--> stefan <--

 

[Bob Barrows]

Trying to get a simple query to ADD 4 values in a query. I am using
the expression builder and all the other BASIC Operators work, i.e *,
-, /.

E.g 4 fields in the query, values 2,4,6,8. When I use the * Operator
I get 384 as the returned value, no problem there.

Expr1: [SPIR_Parts_tbl].[CsI]*[PrI]*[PenI]*[LtI]

But when I use the + Operator I get 2468 as the returned value

You must be storing numbers in Text fields??? Now you see why you should not
be doing that. Numbers should be stored in Number fields.

The problem is: "+" is a dual-purpose operator. If its operands are strings
(as is the case with data stored in Text fields), then it concatenates. If
one operand is a number and the other a string the results depend on the
data itself:
If the string can be converted to a number, addition occurs.
If not, concatenation occurs.

This is why we typically recommend the use of "&" when you want
concatenation to occur: "&" coerces both operands to strings.

If you can't change your table design, the only way around this is: you will
need to explicitly convert your strings to numbers. I don't know if your
data includes fractions, but since you only show us integers, I will
illustrate using the CLng function, which converts data to Long Integer:

Expr1: CLng([SPIR_Parts_tbl].[CsI]) + CLng([PrI]) + CLng([PenI]) +
CLng([LtI])

This is a pain, isn't it? Change your field datatypes to Number so you can
avoid having to jump through this hoop.

 

[Andysgriff]

hi Andy,

Expr1: [SPIR_Parts_tbl].[CsI]*[PrI]*[PenI]*[LtI]
But when I use the + Operator I get 2468 as the returned value

This can only happen if one(all) of your involved fields is a string
(Text).

"2" * "4" * "6" * "8"

* is an arithemtic operator. All values can be converted to a number, so
you get your result.

"2" + "4" + "6" + "8"

+ is a string (Text) operator, too. As all values are text its
concatenates the strings.

CLng("2") + CLng("4") + CLng("6") + CLng("8")

will return your intended sum.


mfG
--> stefan <--

Stefan, great, thanks for the help, its now working

Andy

 

[Stefan Hoffmann]

hi Bob,

This is why we typically recommend the use of "&" when you want
concatenation to occur: "&" coerces both operands to strings.

Not really, e.g.

[Surname] & (", " + [FirstName])

shows the difference between & and + when concatenating strings. It's
about Null handling.


mfG
--> stefan <--

 

[Bob Barrows]

hi Bob,

This is why we typically recommend the use of "&" when you want
concatenation to occur: "&" coerces both operands to strings.

Not really, e.g.

[Surname] & (", " + [FirstName])

shows the difference between & and + when concatenating strings. It's
about Null handling.


mfG
--> stefan <--

Try:
?"3" + 3

?"a" + 3
throws a datatype mismatch error