Text manipulation in update query

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?

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


"Tony in Michigan" wrote:

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?

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


"golfinray" wrote:

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


"Tony in Michigan" wrote:

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?