Anyway to join between two different data types?

Hi all,

I have a Access database that is linking to Oracle tables.

I need to join on two different data types. Number and Varchar2.

With a oracle query, it is easy: one can simply put a to_char on the number
data type. So:

select *
from invoice i, comment c
where to_char(i.invc_id) = c.reference_id

However, I can't see any way to do this with the Access query (in either
Design View or SQL View).

Is this possible?

This will be slow, but have you tried using one of the conversion functions

select *
from invoice i, comment c
where (i.invc_id) & "" = c.reference_id


In a join that could be.
SELECT *
FROM Invoice as I INNER JOIN Comment as C
ON I.Invc_ID & "" = C.Reference_ID

If you were sure that Invc_ID always has a value (never null)
SELECT *
FROM Invoice as I INNER JOIN Comment as C
ON CStr(I.Invc_ID) = C.Reference_ID

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

foghat wrote:
Hi all,

I have a Access database that is linking to Oracle tables.

I need to join on two different data types. Number and Varchar2.

With a oracle query, it is easy: one can simply put a to_char on the number
data type. So:

select *
from invoice i, comment c
where to_char(i.invc_id) = c.reference_id

However, I can't see any way to do this with the Access query (in either
Design View or SQL View).

Is this possible?

select *
from invoice i, comment c
where CStr(i.invc_id) = c.reference_id ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"foghat" wrote:

Hi all,

I have a Access database that is linking to Oracle tables.

I need to join on two different data types. Number and Varchar2.

With a oracle query, it is easy: one can simply put a to_char on the number
data type. So:

select *
from invoice i, comment c
where to_char(i.invc_id) = c.reference_id

However, I can't see any way to do this with the Access query (in either
Design View or SQL View).

Is this possible?

.

Jerry Whittle wrote:
select *
from invoice i, comment c
where CStr(i.invc_id) = c.reference_id ;
Hi all,

[quoted text clipped - 15 lines]

.

Thanks a lot. Works like a charm. I just don't use Access enough, but
figured there must be a to_char equivalent.

Where Oracle uses to_ Access often uses C for Change

CDate = todate

Of course to_char for character isn't quite CStr for change string!

And don't get me started on Oracle vs Access wild cards and usage of single
and double quotation marks!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"foghat" wrote:

Jerry Whittle wrote:
select *
from invoice i, comment c
where CStr(i.invc_id) = c.reference_id ;
Hi all,

[quoted text clipped - 15 lines]

.

Thanks a lot. Works like a charm. I just don't use Access enough, but
figured there must be a to_char equivalent.

.