sql - Stripping out Non Numerical Characters using a Query -

sql - Stripping out Non Numerical Characters using a Query -

light user of ms access not powerfulness user means.

ok, explain want first of all.

i have 2 tables, 1 username xxx99999 ( 3 alpha 5 numeric ) , other 1 99999 ( 5 numeric ).

they 1 in same, part can safely drop first 3 letters , perform need 'link' using lastly 5 numeric digits only.

i imagine doing query.

my question is, how mask build query.

all 5 numeric unique.

if take function @paxdiablo here (vba: how find numbers string), is

public function onlydigits(s string) string ' variables needed (remember utilize "option explicit"). ' dim retval string ' homecoming string. ' dim integer ' counter character position. ' ' initialise homecoming string empty ' retval = "" ' every character in input string, re-create digits ' ' homecoming string. ' = 1 len(s) if mid(s, i, 1) >= "0" , mid(s, i, 1) <= "9" retval = retval + mid(s, i, 1) end if next ' homecoming return string. ' onlydigits = retval end function

paste module , save it, should able link both tables in query (assuming table1 has numbers field , table2 has alpha , numbers):

select table1.myfield1, table2.myfield2 table1 inner bring together table2 on cstr(table1.onlynumbersfield) = onlydigits(table2.textandnumberfield);

this strip alpha characters behind scenes, create sure both datatypes same, "link" them produce joined result in query. know said not powerfulness user , may complicated you, there no "easy" way this. walk through doing through multiple queries, may create more sense you, lot more explain.

sql ms-access-2010