In object-oriented terms the '+' operator is overloaded when applied to strings to provide string concatenation. This is not unusual; the operator is already overloaded to provide addition of different numeric types transparently, because at a low level addition of floating-point values is not the same as addition of long integers, is not the same as addition of currency types, and so on.
In Access the use of '+' is fine if the underlying type to which the operator is being applied is clear. Unbound text boxes, however, will always be treated as string values - just as the value returned by the use of Nz in a query will always be treated as a text value, not a variant. When the underlying type cannot be known by the query engine then text will be assumed, as all displayable types can at least be stored in a text field.
The solution when working with expressions involving Nz in queries or where additions of unbound textbox values is involved is to explicitly typecast them, as NeoPa suggested - and particularly if using Nz, which returns a variant value that the Access query engine will interpret as text. This leads to somewhat clumsy expressions such as
- =CLng(Nz(Sum([A Numeric Value]), 0))
and
- =CLng(Nz([textbox1], 0)) + CLng(Nz([textbox2], 0)) + CLng (Nz ([textbox3], 0))
The use of '+' to mean string concatenation came long before the provision of a separate string concatenation operator (the '&'), and indeed it is itself an overloaded operator, used with a following H to indicate hexadecimal values, for instance
Far from being too dumb it's another example of Access (indeed VB/VBA related systems in general) being too clever!
-Stewart
2,322
