how can I concatenate fields that contain numbers?

[Lorina]

I need to take a series of numbers that are in different fields and combine
them into one number. I am linking to a spreadsheet that contains our UPC
codes. I realize that they need to be text to concanentate, correct? I
can't change the original spreadsheet (it's a UPC number generator and the
fields have to be numbers to create the correct code for new part numbers).
Once I have them in one field in access I am going to link the to the field
for UPC # in our ERP system, subtract them and if the nubmer is > zero I know
someone keyed it incorrectly in our ERP system

any suggestions would be GREATLY appreciated!

 

[KARL DEWEY]

ConcatTextField: [Field1] & [Field2] & [Field3] & [Field4]

 

[KenSheridan via AccessMonster.com]

You can concatenate numeric values in fact, but if the result needs to be of
a specific format, you'd need to cater for that. Say for instance the
concatenated value is made up of three separate numbers which have to be of 3
digits each, with leading zeros where necessary, e.g. 5, 10 and 15 would
become 005010015:

Format([Number1],"000") & Format([Number2],"000") & Format([Number3],"000")

But a second thought occurs to me. If a number does not equal the number in
the ERP system how will you link them to determine the incorrect ones?

Ken Sheridan
Stafford, England

 

[Lorina]

karl
That worked like a charm! So, just so I understand the magic, does putting
ConcatTextField make it treat it as text?

ConcatTextField: [Field1] & [Field2] & [Field3] & [Field4]

--
Build a little, test a little.

I need to take a series of numbers that are in different fields and combine
them into one number. I am linking to a spreadsheet that contains our UPC
codes. I realize that they need to be text to concanentate, correct? I
can't change the original spreadsheet (it's a UPC number generator and the
fields have to be numbers to create the correct code for new part numbers).
Once I have them in one field in access I am going to link the to the field
for UPC # in our ERP system, subtract them and if the nubmer is > zero I know
someone keyed it incorrectly in our ERP system

any suggestions would be GREATLY appreciated!

 

[Bob Barrows]

No, that's just the name of the resulting field.
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.

karl
That worked like a charm! So, just so I understand the magic, does
putting ConcatTextField make it treat it as text?

ConcatTextField: [Field1] & [Field2] & [Field3] & [Field4]

--
Build a little, test a little.

I need to take a series of numbers that are in different fields
and combine them into one number. I am linking to a spreadsheet
that contains our UPC codes. I realize that they need to be text
to concanentate, correct? I can't change the original spreadsheet
(it's a UPC number generator and the fields have to be numbers to
create the correct code for new part numbers). Once I have them in
one field in access I am going to link the to the field for UPC #
in our ERP system, subtract them and if the nubmer is > zero I know
someone keyed it incorrectly in our ERP system

any suggestions would be GREATLY appreciated!

 

[Lorina]

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
subrtact them. Total should be 0 if it's entered correctly in the ERP system.

You can concatenate numeric values in fact, but if the result needs to be of
a specific format, you'd need to cater for that. Say for instance the
concatenated value is made up of three separate numbers which have to be of 3
digits each, with leading zeros where necessary, e.g. 5, 10 and 15 would
become 005010015:

Format([Number1],"000") & Format([Number2],"000") & Format([Number3],"000")

But a second thought occurs to me. If a number does not equal the number in
the ERP system how will you link them to determine the incorrect ones?

Ken Sheridan
Stafford, England

I need to take a series of numbers that are in different fields and combine
them into one number. I am linking to a spreadsheet that contains our UPC
codes. I realize that they need to be text to concanentate, correct? I
can't change the original spreadsheet (it's a UPC number generator and the
fields have to be numbers to create the correct code for new part numbers).
Once I have them in one field in access I am going to link the to the field
for UPC # in our ERP system, subtract them and if the nubmer is > zero I know
someone keyed it incorrectly in our ERP system

any suggestions would be GREATLY appreciated!

--



.

 

[Lorina]

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
subrtact them. Total should be 0 if it's entered correctly in the ERP system.

You can concatenate numeric values in fact, but if the result needs to be of
a specific format, you'd need to cater for that. Say for instance the
concatenated value is made up of three separate numbers which have to be of 3
digits each, with leading zeros where necessary, e.g. 5, 10 and 15 would
become 005010015:

Format([Number1],"000") & Format([Number2],"000") & Format([Number3],"000")

But a second thought occurs to me. If a number does not equal the number in
the ERP system how will you link them to determine the incorrect ones?

Ken Sheridan
Stafford, England

I need to take a series of numbers that are in different fields and combine
them into one number. I am linking to a spreadsheet that contains our UPC
codes. I realize that they need to be text to concanentate, correct? I
can't change the original spreadsheet (it's a UPC number generator and the
fields have to be numbers to create the correct code for new part numbers).
Once I have them in one field in access I am going to link the to the field
for UPC # in our ERP system, subtract them and if the nubmer is > zero I know
someone keyed it incorrectly in our ERP system

any suggestions would be GREATLY appreciated!

--



.

 

[KenSheridan via AccessMonster.com]

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

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]

 

[David W. Fenton]

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. This, for instance, would have to
be substantially more complex to work were it not for Null
propagation with the + operator:

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

The alternative to that requires nested IIf().

There is no non-convoluted way that I can see to propagate Nulls if
you are intending to concatenate numeric values. Since CStr() can't
accept a Null, you're out of luck using Null propagation tricks with
numeric data without additional complication (such as reliance on
something like the ZLSToNull function I posted earlier today).

 

[John W. Vinson]

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???

 

[Bob Barrows]

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 ...

 

[Bob Barrows]

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.

 

[David W. Fenton]

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.

 

[Bob Barrows]

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

 

[David W. Fenton]

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.

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.

 

[Bob Barrows]

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.

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 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?

 

[Lorina]

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:

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

That should do the trick.

Ken Sheridan
Stafford, England

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!

--



.

 

[Lorina]

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!

 

[KenSheridan via AccessMonster.com]

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

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]

 

[David W. Fenton]

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.

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.

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.