Henry Simpson
hsimpson88 at hotmail.com
Thu Jun 5 13:30:01 CDT 2003
I ran into a new one today. Someone built an application where a foreign key is a different datatype than the primary key it refers to. The task at hand was to generate a report that required connecting the tables on the disparate key types. The Primary Key is an autonumber long and the Foreign Key is text type. An attempt to use CStr in the join clause worked though the query designer complained that it couldn't show the join and refused to display that view though it was not greyed out on the toolbar like it is with a union query. A simplified version of the Sql used for testing using CStr in the Join returns correct results in Access 97: SELECT JobCode, JobName FROM tblJob INNER JOIN tblLineItems ON CStr(tblJob.JobID) = tblLineItems.Job; Never having run across this before, I thought it might be useful when merging data from various sources. Ordinarily I would just use an update query to a new numeric field and join on just the numeric fields but there appears to be a fair bit of existing structure that relies on the datatype that was already in place so the disparate join proved convenient. Hen _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com