Convert column Data Type

[Alastair MacFarlane]

Dear Group,

I have 2 tables with the following schema:

Table A:
ID (Integer)
LinkID (String)

Table B:
ID (Integer)
Ref (Long)

I would like to join Table A ==> LinkID <==> Ref <== Table B, but cannot
because one is a String and the Other is a Long.

How can I convert and join all in one query without having to create a
secondary intermediary query with a field like "" & Ref?

Thanks again.

Alastair MacFarlane

 

[Allen Browne]

If you type directly into SQL View of the query, you can typecast the field.

Something like this:

SELECT A.*, B.*
FROM A INNER JOIN B
ON CLng(Val(Nz(A.LinkID,"0"))) = B.Ref;

Explanation:
- CLng() casts to Long, so it matches the Ref.
- Val() converts string to number (and copes with non-numeric values.)
- Nz() is needed because Val() can't handle nulls.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[Alastair MacFarlane]

Allen,

Sorry to take so long to say thank you for your time. I will try out your
suggestion and come back to the group if I have any further questions.

Thanks.

Alastair