Convert column Data Type

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

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alastair MacFarlane" wrote
in message
...
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,

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

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alastair MacFarlane" wrote
in message
...
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