Text manipulation in update query

[Tony in Michigan]

I want to compare two tables, in order to update fields in either table. The
fields that I am matching on are text.
Table 1 field key_num
xxx-1234567-8_nn
Table 2 field key_num
xxx12345678

How can I parse out the hyphens and the _nn portion, so I can match on the
fields?

 

[golfinray]

Mid([table1],1,13) Start at the 1st character and show the next 13.

 

[Duane Hookom]

I think Milton missed a few specs in the question. Bottom line is you want an
update query to match on expressions that are not primary/foreign keys. I
doubt you can get an Access query to do this.

The expression might be
Replace(Replace(Table1.Key_Num,"-",""),"_nn","")

You might be better off creating and updating a new field so you can create
a primary/foreign key relationship.

--
Duane Hookom
Microsoft Access MVP

Mid([table1],1,13) Start at the 1st character and show the next 13.
--
Milton Purdy
ACCESS
State of Arkansas

I want to compare two tables, in order to update fields in either table. The
fields that I am matching on are text.
Table 1 field key_num
xxx-1234567-8_nn
Table 2 field key_num
xxx12345678

How can I parse out the hyphens and the _nn portion, so I can match on the
fields?