Andysgriff said:
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.
