how can I concatenate fields that contain numbers?

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!

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

--
Build a little, test a little.


"Lorina" wrote:

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!

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 wrote:
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!

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

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

"KARL DEWEY" wrote:

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

--
Build a little, test a little.


"Lorina" wrote:

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!

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.

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

"KARL DEWEY" wrote:

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

--
Build a little, test a little.


"Lorina" wrote:

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!

--
HTH,
Bob Barrows

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.

"KenSheridan via AccessMonster.com" wrote:

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 wrote:
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!

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

.

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?

"Lorina" wrote:

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.

"KenSheridan via AccessMonster.com" wrote:

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 wrote:
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!

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

.

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

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

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

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]