Joining tables

[ptrifile]

I am joining 2 tables together and using MEMNUMBER to join the 2, I was wondering if it is possible to ignore the first character and last 2 characters when joining. Example would be:

Member Number on table 1 = D00012303

Member Number on Table 2 = 000123

Is there an easy way to do this?

Thanks for any help

Paul

 

[PHV]

In the SQL view you may either try this:
SELECT ...
FROM Table2 INNER JOIN Table1 ON Table2.MEMNUMBER = Mid(Table1.MEMNUMBER, 2, 6)
WHERE ...
Or this:
SELECT ...
FROM Table2, Table1
WHERE Table2.MEMNUMBER = Mid(Table1.MEMNUMBER, 2, 6) ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ptrifile]

Thanks PHV, that sort of worked. However, I have noticed that not all the Member Numbers are the same length, I need to omit the first number and the last 2 numbers, is this still possible since the number of characters changes?

Thanks again for all your help!

Paul

 

[PHV]

Table2.MEMNUMBER = Mid(Table1.MEMNUMBER, 2, Len(Table1.MEMNUMBER)-3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886