Convert text to number in a self join query?

[Guest]

I am trying to perform a self join query to link. The manager's name is a
number, and I need to make a query that shows the supervisor name. I have
the basic self join figured out (see SQL statement below), but I'm having
troubles with the data type.

The problem is that the datatype of the inter-related columns are not the
same type. The [em_number] is stored as text, and the [reportsto] is stored
as a number. I don't have permissions to change the datatype in the table,
so I'm left to try to convert it in the following SQL statement.

SELECT a.Name_last, a.Name_first, b.Name_last AS ‘ManagerName’
FROM dbo_tmp_Archibus AS a INNER JOIN dbo_tmp_Archibus AS b ON
a.reportsto=b.em_number;

where:
[dbo_tmp_Archibus] is the table
[reportsto] is the supervisor, stored as a number and
[em_number] is the employee number, which is stored as text for some bizarre
reason

 

[Guest]

Try
SELECT a.Name_last, a.Name_first, b.Name_last AS ‘ManagerName’
FROM dbo_tmp_Archibus AS a INNER JOIN dbo_tmp_Archibus AS b ON
cStr(a.reportsto) = b.em_number;

Hope This Helps
Gerald Stanley MCSD