Join tables using different datatypes

[n2nuk]

Hi there,

I am having difficulty joining two tables where the foreign key on table2 is of type text and not number. The join to table1 is using the primary key and is therefore of type number. When I run the query I get a type mismatch error.
The tables are linked tables from a sybase database from our live client application.
Any ideas how I convert the datatype to number 'on the fly' when running the query?

many thanks

naz

 

[hwkranger]

I'm not sure if you can do this in access. Does Sybase have views? I'm not familiar with Sybase at all, but in SQL Server if I had this problem I would make a view, and re-cast the data as the type I wanted. (recast = format)

The link the view instead of the table.

Hope this helps.



Randall Vollen
National City Bank Corp.

 

[lespaul]

or use the char function? Also, i'm not sure about working in Access Jet SQL, but I would:

SELECT * FROM tblA INNER JOIN tblB on Char(tblA.Numberkey) = tblB.AlphaKey

Leslie

 

[PHV]

Take a look at the CLng function.

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