String Manipulation in an Inner Join

[joave]

Hi:

I am attempting to reverse the name in an inner join so it correlates
properly with the other table but am having difficulty - here is my code -
research has not turned up much help:

SELECT Name
FROM Reps INNER JOIN PDTable
ON (SELECT Lcase(Right$([Name],Len([Name])-InStr(1,[Name]," ")) & ", " &
Left$([Name],InStr(1,[Name]," ")-1)) FROM Reps) Like PDTable.[Created By] &
"*";

Any help would be appreciated.

Thank you,

Dave

 

[John Spencer]

Easiest way to do this would be to create a query on the one table with
calculated fields that show as you wish to use them in the match.

SELECT Trim(Mid([Name],Instr(1,[Name]," ")+1) & ", " &
Left([Name],Instr(1,Name & " "," ")-1)) as FixedName
FROM Reps

Now use that saved query as if it were a table and join to it.

SELECT PDTable.[Name]
FROM SavedQuery as Q INNER JOIN PDTable
ON Q.FixedName Like PDTable.[Created BY] & "*"

If the values were exactly equal, you could use the alternative
SELECT [Name]
FROM PDtable
WHERE PDTable.[Created by] IN
(
SELECT Trim(Mid([Name],Instr(1,[Name]," ")+1) & ", " &
Left([Name],Instr(1,Name & " "," ")-1)) as FixedName
FROM Reps
)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================