Inner join on a partial column in Access -

i'm attempting join 2 tables in ms access 2010 join condition part of(a.col1) = b.col2. i've not been able figure out how far.

my 2 tables have these critical columns:

1. table 1 has column icd9 code-description* values like:
   842.00 - sprain/strain, wrist
924.11 - contusion, knee
2. table 2 has column dx values like:
   842
924.11

i have tried these 2 join criteria:

from table1    inner join table2 on table1.replace(ltrim(replace(left(icd9code-description],                            (instr(1,[icd9code-description]," "))-1),"0"," "))," ","0")                          = table2.dx 

and

select icd9   table2 inner join         (select replace(ltrim(replace(left([icd9 code-description],               (instr(1,[icd9 code-description]," "))-1),"0"," "))," ","0") icd9           table1)        on diag.dx = icd9 

neither of access likes.

i'd avoid pulling out join criteria portion own column in table1 if @ possible.

what access way of doing this?

*don't hate me column name. didn't create it, have support it.

the val() function "returns numbers contained in string numeric value of appropriate type." (see val function topic in access' built-in system.)

the "neat thing" situation reads characters string until encounters character can't part of valid number. doesn't throw error. keeps numeric characters it's collected , ignores rest.

here's 2 of examples in immediate window ...

? val("842.00 - sprain/strain, wrist")  842  ? val("924.11 - contusion, knee")  924.11  

so val() should make join simpler, though need apply on table2.dx strings ...

from     table1 inner join table2     on val(table1.[icd9code-description]) = val(table2.dx)