Join error is query (table linked to SQL server view)

[Norwich]

Hi,

I'm trying to run a simple query in access 2000 but it comes up with the error:

cannot join on Memo, OLE, or Hyperlik Object (source1.pcode=dbo_source2.zipcode)

where the query is:

SELECT source1.*, dbo_source2.*
FROM source1 INNER JOIN dbo_source2 ON source1.PCODE = dbo_source2.ZIPCODE;


Now, source1.pcode is a simple, short 8 character text field. However, dbo_source2.ZIPCODE is from an ODBC link to an SQL Server view. This ZIPCODE in the view is created with an MSSQL query using the REPLACE function to pull out extraneous characters but when run in query analyser, seems to return a 255 length string - even though the source is < 10 characters.

Is there a way to get around this in the access query?

Thanks

 

[mwolf00]

cannot join on Memo, OLE, or Hyperlik Object (source1.pcode=dbo_source2.zipcode)
I'd guess that and ODBC link is considered OLE

&quot;but when run in query analyser, seems to return a 255 length string - even though the source is < 10 characters.&quot;

SELECT CAST(REPLACE(fieldName,'characters','') AS CHAR(8)) FROM myTable
-----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[Norwich]

thanks