how can I concatenate fields that contain numbers?

David W. Fenton wrote:
"Bob Barrows" wrote in
:

It's the concatenation operators (&) that do the magic. & coerces
both operands to strings before concatenating them, unlike the +
operator, which really should not be used for concatenation.

The + operator is INCREDIBLY USEFUL for concatenation of non-numeric
data because it propagates Nulls.

I knew someone would mention this so I didn't bother. :-)

I've never really had a need for that so it's really not at the top of my
list of incredibly useful functionality ...
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob Barrows wrote:
David W. Fenton wrote:
"Bob Barrows" wrote in
:

It's the concatenation operators (&) that do the magic. & coerces
both operands to strings before concatenating them, unlike the +
operator, which really should not be used for concatenation.

The + operator is INCREDIBLY USEFUL for concatenation of non-numeric
data because it propagates Nulls.

I knew someone would mention this so I didn't bother. :-)

I've never really had a need for that so it's really not at the top
of my list of incredibly useful functionality ...

I meant to add that IMO, this usefullness is very much offset by the
unpredictable results when one doesn't know or pay attention to what one is
doing. This of course does not apply to you David.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

"Bob Barrows" wrote in
:

David W. Fenton wrote:
"Bob Barrows" wrote in
:

It's the concatenation operators (&) that do the magic. &
coerces both operands to strings before concatenating them,
unlike the + operator, which really should not be used for
concatenation.

The + operator is INCREDIBLY USEFUL for concatenation of
non-numeric data because it propagates Nulls.

I knew someone would mention this so I didn't bother. :-)

I've never really had a need for that so it's really not at the
top of my list of incredibly useful functionality ...

You don't find the name concatenation trick really cool? I.e., this:

FullName: Mid(("12"+LastName) & (", "+FirstName), 3)

The alternative is:

LastName & _
IIf(Not IsNull(LastName) And Not IsNull(FirstName),", ") _
& FirstName

That's way more convoluted and once the Mid() method with Null
propagation was pointed out to me (I think it was Terry Kreft or
Trevor Best or someone of that ilk), I have capitalized on Null
propagation wherever possible.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David W. Fenton wrote:
"Bob Barrows" wrote in
:

David W. Fenton wrote:
"Bob Barrows" wrote in
:

It's the concatenation operators (&) that do the magic. &
coerces both operands to strings before concatenating them,
unlike the + operator, which really should not be used for
concatenation.

The + operator is INCREDIBLY USEFUL for concatenation of
non-numeric data because it propagates Nulls.

I knew someone would mention this so I didn't bother. :-)

I've never really had a need for that so it's really not at the
top of my list of incredibly useful functionality ...

You don't find the name concatenation trick really cool? I.e., this:

I didn't say that: I said I've never been in a position to need to use it.

The cool trick I have used is the one where you avoid problems with Nulls by
simply concatenating (with &) an empty string ("") to the values ... for
some reason, concatenation is unlike any other operation in that the
presence of Null in one of the operands does not force the result to be
Null.... so, yes, that's a cool trick and I do use it.

I also said it is kinda cool, but I think the coolness might be offset by
the risks of unexpected results if used by someone who is not paying
attention or doesn't know about the risks involved ... again, this does not
apply to you. :-)


FullName: Mid(("12"+LastName) & (", "+FirstName), 3)

The alternative is:

LastName & _
IIf(Not IsNull(LastName) And Not IsNull(FirstName),", ") _
& FirstName

That's way more convoluted and once the Mid() method with Null
propagation was pointed out to me (I think it was Terry Kreft or
Trevor Best or someone of that ilk), I have capitalized on Null
propagation wherever possible.

It may be more convoluted, but it's probably very close to what is actually
going on behind the scenes :-)


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

"Bob Barrows" wrote in
:

David W. Fenton wrote:
"Bob Barrows" wrote in
:

David W. Fenton wrote:
"Bob Barrows" wrote in
:

It's the concatenation operators (&) that do the magic. &
coerces both operands to strings before concatenating them,
unlike the + operator, which really should not be used for
concatenation.

The + operator is INCREDIBLY USEFUL for concatenation of
non-numeric data because it propagates Nulls.

I knew someone would mention this so I didn't bother. :-)

I've never really had a need for that so it's really not at the
top of my list of incredibly useful functionality ...

You don't find the name concatenation trick really cool? I.e.,
this:

I didn't say that: I said I've never been in a position to need to
use it.

You don't have database where you have to present lists of Lastname,
Firstname? If so, you work in a very different environment than I,
as I don't have any apps where I *don't* have to do that!

The cool trick I have used is the one where you avoid problems
with Nulls by simply concatenating (with &) an empty string ("")
to the values ... for some reason, concatenation is unlike any
other operation in that the presence of Null in one of the
operands does not force the result to be Null.... so, yes, that's
a cool trick and I do use it.

My understanding from something that someone smart told me (MichKa?)
is that concatenating a Null with a ZLS is a performance hog. This
only matters in a loop, though.

I also said it is kinda cool, but I think the coolness might be
offset by the risks of unexpected results if used by someone who
is not paying attention or doesn't know about the risks involved
... again, this does not apply to you. :-)

I don't understand why you would suggest someone should not use
something just because stupid people might get it wrong. If that
were the case, you'd be telling everyone to turn off their computers
entirely.

FullName: Mid(("12"+LastName) & (", "+FirstName), 3)

The alternative is:

LastName & _
IIf(Not IsNull(LastName) And Not IsNull(FirstName),", ") _
& FirstName

That's way more convoluted and once the Mid() method with Null
propagation was pointed out to me (I think it was Terry Kreft or
Trevor Best or someone of that ilk), I have capitalized on Null
propagation wherever possible.

It may be more convoluted, but it's probably very close to what is
actually going on behind the scenes :-)

Behind the scenes in *what*? Using Null propagation works entirely
differently, as it uses the string handling functions of VBA that
are under the hood, rather than passing the thing through two extra
function calls, and still requiring a dependence on VBA string
handling for non-Null-propagating concatenation.

It seems like no contest to me.

And even if what happens behind the scenes *were* exactly the same
(and I think it quite clear that it is *not*), it would still be
better to use the non-convoluted solution, since it's easier to
debug/maintain, and takes fewer characters to type.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David W. Fenton wrote:
You don't have database where you have to present lists of Lastname,
Firstname? If so, you work in a very different environment than I,
as I don't have any apps where I *don't* have to do that!

Nope. I guess it is surprising, huh? I guess it's because the databases I
inherited rarely allow nulls, especially in firstnae, latsname fields. I've
never had to create a report that concatenates them.


The cool trick I have used is the one where you avoid problems
with Nulls by simply concatenating (with &) an empty string ("")
to the values ... for some reason, concatenation is unlike any
other operation in that the presence of Null in one of the
operands does not force the result to be Null.... so, yes, that's
a cool trick and I do use it.

My understanding from something that someone smart told me (MichKa?)
is that concatenating a Null with a ZLS is a performance hog. This
only matters in a loop, though.

It certainly is, and I would not consider using it in a loop.


I also said it is kinda cool, but I think the coolness might be
offset by the risks of unexpected results if used by someone who
is not paying attention or doesn't know about the risks involved
... again, this does not apply to you. :-)

I don't understand why you would suggest someone should not use
something just because stupid people might get it wrong. If that
were the case, you'd be telling everyone to turn off their computers
entirely.

True, and I'm not suggesting they don't use it ... only that they be aware
of the risks before they do. :-)

With that in mind, the syntactic sugar we're discussing certainly has no
downside.

It seems we are definitely in violent agreement. shall we settle this with
pistols at dawn? :-)
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Ken - I feel like I am in so close to getting a gold medal in the
concatenating olympics here!! I got my numbers to subtract correctly ( I
just had to play around with the brackets and parenthesis in your example a
bit). My next hurdle is I only want to return when it's Not equal to 0 (or,
in other words, those that are not identical in the 2 records). So I tried
not equal to 0, greater than 1 or less than 1, etc and each time I do that I
get an error message that says "data type mismatch in
criteia expression. Any suggestions? I am SO close to getting a tool
that will be fantastic for our company!
"KenSheridan via AccessMonster.com" wrote:

You can apply the Val function to each to return a number:

Val(ERPNumber]])-Val([SpreadsheetNumber])

That should do the trick.

Ken Sheridan
Stafford, England

Lorina wrote:
Bob -
Well....I see I have a problem! I just tried to do my math and it isn't
working. The field in the ERP is text and I am assuming my concatenated
field is also. Is there any way you know of to compare them ? A way to be
able to subtract them?

Both the ERP system and the spreadsheet contain the part number. That will
be my common field. I will pull the UPC for each data source and then
[quoted text clipped - 24 lines]

any suggestions would be GREATLY appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

.

I have a spreadsheet that has UPC # but each numer is in a cell. In our ERP
system it's listed as one number. I want to vailidate that it's entered
correctly in ERP system. I pulled in the spreadsheet and concatenated the
cells (with help from Ken on this message board). In a new query I linked
the query above to the part master field in our ERP system. I want to
subtract them and, if entered problerly, the difference = 0. That part
works. Now I want to have it only return results when they are 0 (only
those that are incorrectly entered into the ERP system). When I put in 0
in the criteria it tells me there is a data mismatch!

"John W. Vinson" wrote:

On Fri, 19 Feb 2010 11:36:01 -0800, Lorina
wrote:

Well....I see I have a problem! I just tried to do my math and it isn't
working. The field in the ERP is text and I am assuming my concatenated
field is also. Is there any way you know of to compare them ? A way to be
able to subtract them?

Please post an example of the data. What does it mean to subtract a UPC code?
That's sort of like subtracting Social Security numbers or phone numbers,
isn't it???
--

John W. Vinson [MVP]
.

Sorry about the mess-up over the bracketing in my last post. Can't think how
that happened! Try one of the following:

Val([ERPNumber])-Val([SpreadsheetNumber]) 0

or:

Val([ERPNumber]) Val([SpreadsheetNumber])

Ken Sheridan
Stafford, England

Lorina wrote:
Ken - I feel like I am in so close to getting a gold medal in the
concatenating olympics here!! I got my numbers to subtract correctly ( I
just had to play around with the brackets and parenthesis in your example a
bit). My next hurdle is I only want to return when it's Not equal to 0 (or,
in other words, those that are not identical in the 2 records). So I tried
not equal to 0, greater than 1 or less than 1, etc and each time I do that I
get an error message that says "data type mismatch in
criteia expression. Any suggestions? I am SO close to getting a tool
that will be fantastic for our company!

You can apply the Val function to each to return a number:

[quoted text clipped - 16 lines]

any suggestions would be GREATLY appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

"Bob Barrows" wrote in
:

David W. Fenton wrote:
You don't have database where you have to present lists of
Lastname, Firstname? If so, you work in a very different
environment than I, as I don't have any apps where I *don't* have
to do that!

Nope. I guess it is surprising, huh? I guess it's because the
databases I inherited rarely allow nulls, especially in firstnae,
latsname fields. I've never had to create a report that
concatenates them.

Huh. I can't imagine a table storing data about people that doesn't
allow Nulls in those fields. If you know there are no Nulls, you
don't need the complicated nested IIf() version, either, you can
just concatenate LastName & ", " & FirstName and be done with it.

Of course, I use the same trick with displaying name and company:

Mid(("12"+LastName) & (", "+FirstName) & (": "+Company), 3)

....so it might still be useful with no Nulls in the name fields,
since Null propagation would avoid having to test if company is
Null. If you know the name fields are always filled out, you could
get rid of the Mid() wrapper and use:

LastName & ", " & FirstName & (": "+Company)

But maybe you don't store company name that way and wouldn't need
it.

The cool trick I have used is the one where you avoid problems
with Nulls by simply concatenating (with &) an empty string ("")
to the values ... for some reason, concatenation is unlike any
other operation in that the presence of Null in one of the
operands does not force the result to be Null.... so, yes,
that's a cool trick and I do use it.

My understanding from something that someone smart told me
(MichKa?) is that concatenating a Null with a ZLS is a
performance hog. This only matters in a loop, though.

It certainly is, and I would not consider using it in a loop.

I try to use the most efficient methods even for one-off lines of
code that aren't inside a loop because someday the code might get
changed so that that line of code ends up in a loop. Also, it
develops the habit of using the most-efficient method at all times
so you will choose it when you *do* need the efficiency, and so that
you don't have to store the rules in your head as to when to use it
and when not!

Interestingly, that latter is very similar to your reservations
about using the + concatenation operator for Null propagation. I
have no problem storing the rule for using it (use it only with
non-numeric data), but you suggested many people would not do well
with it.

I also said it is kinda cool, but I think the coolness might be
offset by the risks of unexpected results if used by someone who
is not paying attention or doesn't know about the risks involved
... again, this does not apply to you. :-)

I don't understand why you would suggest someone should not use
something just because stupid people might get it wrong. If that
were the case, you'd be telling everyone to turn off their
computers entirely.

True, and I'm not suggesting they don't use it ... only that they
be aware of the risks before they do. :-)

I have some difficulty imagining situations where one would
concatenate numeric data without realizing one couldn't use the +
operator.

With that in mind, the syntactic sugar we're discussing certainly
has no downside.

It seems we are definitely in violent agreement. shall we settle
this with pistols at dawn? :-)

No! I don't want this classic agreement dispute to end!

I've often said it's no fun at all to argue with people with whom
you really truly disagree -- it's only fun when you're 90% in
agreement.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/