Convert text to number in a query to enable join

[mbnu99]

New to this wek site and forum but as I've been chucked in at the deep end with access I'd appreciate some help.

Before I say anything more I though I'd better say I'm no SQL expert!

All the tables in my DB are linked SQL ones, I have two tables in a query and need to join a text field (it only has numbers in it) to another field that is formatted as a number. I tried using CustID1:Format([CustID],"#") but whilst it appears to work in the query itself when you make the join I still get "type missmatch"

Hope someone can point me in the right direction.
Regards
Martyn

 

[PHV]

ON CLng([text field]) = [numeric field]

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

 

[mbnu99]

Thank you for the reply, very much appreciated

I've copied your suggestion into my query grid in access so it looks like:
ON CLng([CustID]) = [numeric field]

and I've tried:
CustID2: CLng([CustID1])=[numeric field]

Neither of which work.... I've been on MS's support site to look up the CLng but can not find any access reference to it. Am I missing something?

Regards
Martyn

 

[PHV]

Can you post the SQL code generated when you do the join between the text field and the numeric field, saying us which is the numeric one ?

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