Concatenate (?) versus sum (+) clarification

[TheSmileyCoder]

I was trying to look at this post Summing Fields if not null
and to test it I created an unbound form, with 4 unbound text fields, and a calculated field, with the control

source:=nz([Tekst1];0)+nz([Tekst2];0)+nz([Tekst3];0)+nz([Tekst4];0)

I was a bit surprised to see that when i wrote 1 in the first field 2 in the second and so on, that my calculated field came out as 1234 instead of 10 as expected.

Im guessing that access not knowing better since the fields were all unbound figured I wanted to concatenate it (which seems dumb when we have an operator spefically for that purpose the &).

Does anyone have any other insight to offer on this, and how to avoid access using the + to concatenate fields?

[NeoPa]

Absolutely Smiley :)

The + character as a concatenator is not redundant. It is not the same as the & (See Using "&" and "+" in WHERE Clause).

To avoid any ambiguity it is necessary to convert (at least) the first value to a numeric value. Use something like :

Val(Nz([Field1],0))

Now you've pointed that out though, I'll need to update my own suggestion in that thread to handle this little side-effect :(

[Stewart Ross]

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

Const cHexChar = &H25AA

Far from being too dumb it's another example of Access (indeed VB/VBA related systems in general) being too clever!

-Stewart

[TheSmileyCoder]

Good answers both of you, but I had to choose just one. Thank you both!

[NeoPa]

No foul.

If mine was good, Stewart's was excellent. I'd have chosen his :D

[Stewart Ross]

Thank you both for your kind comments!

:)

-Stewart