SQL to_number in join

[mcinnes01]

Hi,

I am trying to join to tables using the Employee ID field.

Both are views from other databases (previously never linked) one holds the value as number and the other as a text string.

I have tried using the to_number function but keep getting the error
"JOIN expression not supported."

This is my statement:

SELECT PEND.*
FROM PEND INNER JOIN StaffList ON PEND.EMPID = to_number('StaffList.ID')

I can't change the tables as they are views and also everything else is linked up using the current formats, so it wouldn't be feasible to change it at table level as this would affect other applications.

Hope you can help,


Andy

 

[sxschech]

Is your query a SQL Passthru (connecting directly to an odbc source) or is it a standard access query? If a regular access query then try the following:

SELECT PEND.* FROM PEND INNER JOIN StaffList ON PEND.EMPID = val(StaffList.ID)

If odbc, did you try the
to_number('StaffList.ID') without the quotes?
to_number(StaffList.ID)