asked on

Update or Append Data to a New Field in the Same Table

Hi Experts -

I'm trying to update a field in a table with the correct view; that is to show all Part Numbers in one field. If you look at my sample2 database; I have a field called PartNumber and one called PartNumber2 I would like both of these numbers in the same field named NewPartNumber in the same table.

Should I use an Update or Append query for this?

Thanks in Advance,
GeneBat
sample2.mdb

Rey Obrero (Capricorn1)

use an update query

update tablename
set [NewPartNumber]=[partnumber1] & [partnumber2]

how exactly do you want to show the newPartnumber?

GeneBat

ASKER

Cap -
I forgot to write this; I don't want the RBB_Vendor text in the new field just numbers only.

Rey Obrero (Capricorn1)

post sample result..

GeneBat

ASKER

here's a sample result. if you run the update query it put's everything in the field and not just the numbers only.
sample2.mdb

Rey Obrero (Capricorn1)

try this query

UPDATE sample SET sample.NewPartNumber =IIF(isnumeric([PartNumber]),[PartNumber] & [PartNumber2],[PartNumber2])

GeneBat

ASKER

Darn, the real data could have a alpha and numeric in the PartNumber and PartNumber2 field see sample2.

Sorry about this Cap1?
sample2.mdb

Helen Feddema

Generally, it is better to do this kind of thing in a query, because if you write the concatenated value to a field in a table, and one of the components changes, the concatenated value will be incorrect.

Rey Obrero (Capricorn1)

<Darn, the real data could have a alpha and numeric in the PartNumber and PartNumber2 field see sample2. >

so, what do you want to happen, don't make me keep guessing

state the rules that need to be followed... in plain english

post sample values and the result you want

GeneBat

ASKER

Hi Helen_Feddema -

Thanks; I'm trying to get the query correct as in the same database I have attached.

GeneBat

ASKER

What we're doing is correct it's just the the database field PartNumber could have a number that looks like this 13J13 so; isnumeric would not work.

PartNumber PartNumber2 NewPartNumber
13J13 13J13
RRB_Vendor 14R14 14R14
15T15 15T15

Like the example above.
Thanks in advance.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GeneBat

ASKER

No there aren't...
Wonderful!
It works great!
Thanks so much Cap1.