plus sign applied as &

[zofficedepot]

Access 2003 (probably other versions too) - Bless their hearts,
Microsoft assumes that when you use a plus sign between two text
fields in a query, that you MEANT to type an & but were too stupid to
do so. Thank you, MS developers - I sleep so much better with the
comfort that you know what I want to do, better than I do, and coerce
my cooperation. Jawohl, mein Kommandant!

Sure, I was using a text field. Yes, I enjoy and take advantage of
implied conversions and such - but it's that dogmatic coercion that is
most lame and offensive (on everything MS touches, BTW). I'm hoping
that this is one of those cases where by some miracle of goodness that
I can escape the coercion? Is there an option for overriding this one?

BTW the minus sign is not afflicted. So maybe the answer is to coerce
numeric conversion with double minuses.

Example:
Table1 has fld1,fld2,fld3,fld4 each left at default declaration as
Text(50).

SELECT [fld1]-[fld2]-[fld3]-[fld4] AS foo, [fld1]-([fld2]+[fld3]+
[fld4]) AS bar, [fld2]+[fld3]+[fld4] AS snafu
FROM Table1;

foo <> bar. Lovely.

 

[Marshall Barton]

Access 2003 (probably other versions too) - Bless their hearts,
Microsoft assumes that when you use a plus sign between two text
fields in a query, that you MEANT to type an & but were too stupid to
do so. Thank you, MS developers - I sleep so much better with the
comfort that you know what I want to do, better than I do, and coerce
my cooperation. Jawohl, mein Kommandant!

Sure, I was using a text field. Yes, I enjoy and take advantage of
implied conversions and such - but it's that dogmatic coercion that is
most lame and offensive (on everything MS touches, BTW). I'm hoping
that this is one of those cases where by some miracle of goodness that
I can escape the coercion? Is there an option for overriding this one?

BTW the minus sign is not afflicted. So maybe the answer is to coerce
numeric conversion with double minuses.

Example:
Table1 has fld1,fld2,fld3,fld4 each left at default declaration as
Text(50).

SELECT [fld1]-[fld2]-[fld3]-[fld4] AS foo, [fld1]-([fld2]+[fld3]+
[fld4]) AS bar, [fld2]+[fld3]+[fld4] AS snafu
FROM Table1;

+ and & perform (slightly) different operations when applied
to Text fields.
"a" & Null result is "a"
"a" + Null result is Null
This can be especially useful when a field (e.g. middle
initial) sould not be preceded by a separator when it is
Null. FN & (" " + MI) & LN

If you want to do arithmetic on a field, then the field
should be a numeric type instead of Text.

 

[zofficedepot]

Access 2003 (probably other versions too) - Bless their hearts,
Microsoft assumes that when you use a plus sign between two text
fields in a query, that you MEANT to type an & but were too stupid to
do so. Thank you, MS developers - I sleep so much better with the
comfort that you know what I want to do, better than I do, and coerce
my cooperation. Jawohl, mein Kommandant!

Sure, I was using a text field. Yes, I enjoy and take advantage of
implied conversions and such - but it's that dogmatic coercion that is
most lame and offensive (on everything MS touches, BTW). I'm hoping
that this is one of those cases where by some miracle of goodness that
I can escape the coercion? Is there an option for overriding this one?

BTW the minus sign is not afflicted. So maybe the answer is to coerce
numeric conversion with double minuses.

Example:
Table1 has fld1,fld2,fld3,fld4 each left at default declaration as
Text(50).

SELECT [fld1]-[fld2]-[fld3]-[fld4] AS foo, [fld1]-([fld2]+[fld3]+
[fld4]) AS bar, [fld2]+[fld3]+[fld4] AS snafu
FROM Table1;

+ and & perform (slightly) different operations when applied
to Text fields.
"a" & Null result is "a"
"a" + Null result is Null
This can be especially useful when a field (e.g. middle
initial) sould not be preceded by a separator when it is
Null. FN & (" " + MI) & LN

If you want to do arithmetic on a field, then the field
should be a numeric type instead of Text.

(Clarifying my original post: the term coercion was used in 2
different senses - one in despotic "dictating user results," and the
other regarding data forcing numeric type. Sorry.)

Well sure it's easy to say to use numeric field types, but in the real
world it's quite common to have text fields with numbers; no doubt,
this was a (or "the") reason for [some of] the supplied conversion
"conveniences." If you want to strictly say "play by the rules," then
using plus sign should strictly be an error. Anyway, I would dearly
love to be able to override it using plus as an ampersand. The example
above shows a true horror resulting.

But I'm interested in your example too. Thank you for sharing that
useful tip. Yet in the [Null] absence of a middle initial, would you
want to use "" & MI giving "" rather than "" + MI giving Null? I.e.,
would you want "John" & "Doe" vs. "John" & Null & "Doe"

 

[Marshall Barton]

+ and & perform (slightly) different operations when applied
to Text fields.
"a" & Null result is "a"
"a" + Null result is Null
This can be especially useful when a field (e.g. middle
initial) sould not be preceded by a separator when it is
Null. FN & (" " + MI) & LN

If you want to do arithmetic on a field, then the field
should be a numeric type instead of Text.

(Clarifying my original post: the term coercion was used in 2
different senses - one in despotic "dictating user results," and the
other regarding data forcing numeric type. Sorry.)

Well sure it's easy to say to use numeric field types, but in the real
world it's quite common to have text fields with numbers; no doubt,
this was a (or "the") reason for [some of] the supplied conversion
"conveniences." If you want to strictly say "play by the rules," then
using plus sign should strictly be an error. Anyway, I would dearly
love to be able to override it using plus as an ampersand. The example
above shows a true horror resulting.

I have trouble with "in the real world it's quite common to
have text fields with numbers". IMO, using the "correct"
data type for each field is an important design decision.
Again IMO, I think that using Text for everything is just
asking for problems.

If you insist on using Text fields for truely numeric data
(not Soc. Sec. or Zip codes, etc), then you should at least
explicitly cast the field to the type needed in each
expression. E.g. CCur(txtAmount) * CLng(txtQuantity)

But I'm interested in your example too. Thank you for sharing that
useful tip. Yet in the [Null] absence of a middle initial, would you
want to use "" & MI giving "" rather than "" + MI giving Null? I.e.,
would you want "John" & "Doe" vs. "John" & Null & "Doe"

I don't follow your example. Without a separator characater
between the names, not only does the point I was trying to
make not arise, but the result is near meaningless.

 

[zofficedepot]

I appreciate your time but I would rather hear from someone who
acknowledges the realities here and can contemplate a workaround. The
fact is that automatic conversions are supplied, and supplied for a
reason. Those who preach the "strict" card can strictly live by the
strict card. If you want to strictly say "play by the rules," then
using plus sign should strictly be an error!

Anyway, casting is obviously one approach. Another thing I see from
time to time is "0+Field1" - which I suspect is just a lazy, quicker-
to-type workaround. The double unary numeric conversion coercion on
Booleans is an accepted fact of life in advanced Excel usage. If
someone has pertinent knowledge of its appropriateness and performance
(reward or cost) in Access I would be interested.

I wasn't attacking your example, but simply reading it to say the
opposite of what would seem logical. I thought that
& NULL &
should be avoided in favor of
& "" &

However I now learn that you can string-concatenate a Null without
penalty. (I also thought you were showing an empty string "" rather
than the space " " character - my mistake.) Now I see that that is a
clever middle initial (moreover, optional string component) item. Good
one.