replace function in SELECT statement

[dharkangel]

hello everyone,
I have this query I am trying to do and am getting a 'data type mismatch in criteria expression' error. I need to remove the commas from the Assigned_To field in order to successfully compare it to the UserName field. Just a little more background, the TokyoInteralActions table is a linked table that is going to be updated daily, therefore, I cannot correct the comma problem at the time those tables are created. An example of the data is this:

Assigned_To
-----------
Smith, Joe
Umber, Ted

UserName
-----------
Smith Joe
Umber Ted

SELECT TokyoInternalActions.Issue_ID
FROM TokyoInternalActions INNER JOIN UserRoleData ON Replace(TokyoInternalActions.Assigned_To,',','')=UserRoleData.UserName;

 

[r937]

the replace function looks okay, but you'll probably get better replies in a forum for whatever database you're using

this is the ANSI SQL forum

r937.com | rudy.ca