How do I link a passthrough memo field to local text table? 1

[calandrelli]

I have a passthrough query from an Oracle 9 datasource that stores a 4 didit department number in a field that Access imports as a memo field. (I don't know why memo for 4 characters). I have a local tabe in my Access 2000 database that will provide a department mgr name for each department. When I try to build the query it won't let me link a text and memo field. Is there anyway around this? Can I force Access to convert the field type on import?

 

[PHV]

Can't you cast the department number in your passthrough query ?

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

 

[calandrelli]

I don't understand what you mean by cast...The field C_2 is the memo field. This SQL is in a passthrough query using ODBC Oracle 9.2.

select T1."OP_TYP_DESC" , T2."PCKG_NBR" , T3."PCKG_STS_DESC" , Trunc(T2."PCKG_CURR_STATUS_DT") ,Trunc(T2."PCKG_APPROVED_DATE"), Trunc(T2."START_DT") , T2."CURRENT_VERSION" , Trunc(T2."PCKG_CLOSED_DATE") ,T4."DOC_NM", T4."C_2"
from "IDEA"."EPAS_OP_TYP" T1, "IDEA"."EPAS_PCKG_HDR" T2, "IDEA"."EPAS_STS_CD" T3, "IDEA"."FORM53_VW_3" T4
where T1."OP_TYP_KEY"=T2."OP_TYP_KEY" and T3."PCKG_STS_KEY"=T2."PCKG_STS_KEY" and T2."PCKG_KEY"=T4."PCKG_KEY" and T1."OP_TYP_DESC"='OP A509'

When I try joining C_2 with a text field from a local table I get the error...

"Cannot join on Memo, OLE, or Hyperlink Object <name>. (Error 3118)
You cannot join Memo or OLE Object fields. The field you use to logically tie two or more tables together must be some other data type, such as text or integer. "

 

[PHV]

I don't know Oracle's dialect, but in ANSI SQL:
CAST(T4.C_2 AS CHAR(4))

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

 

[calandrelli]

It worked perfect. You saved me a lot of work. I was going to create make tables locally and then change the local table field from memo to text. Thank You !!!!!!