Ignoring leading zeros in join query

[Deniseobx]

Hi everyone,

I have 2 linked tables from 2 different databases, there is a common field in both: “CORP_NBR” I need this field in order to create a join, but the problem is that in one table this field stores leading zeros and in the other table it doesn’t. I can see in design view that this field is text type in both tables. I cannot change field types because I don’t own the objects.

Is there a way or a function that I can add in my join query to ignore leading zeros?

I appreciate your help

 

[KeithG]

what you can do is go into the SQL view of the query, locate the from caluse and add a "0" &[FieldName] to the field in the join missing the 0. If you dont understand just post your SQL and I will look at it.

 

[Deniseobx]

Would that work for any number of leading zeros? some record have 1,2,3,0r even 4 leading zeros, and maybe more, there are about 30,000 records.

 

[KeithG]

No, you didn't mention that. What about converting both fields a numeric value in the Join Expression so the zero's are dropped?

cLng([Field])=cLng([Field])

 

[Deniseobx]

Thanks Keith that's exactly what I am trying to do, I changed my query using the function you sugested, but I am getting a error that says "invalid use of null", the following is my query, could you please take a look and see what I did wrong ??? Thanks in advance.


SELECT *
FROM CORP_CUST_DTL LEFT JOIN NSUsers ON CLNG (CORP_CUST_DTL.CORP_PRIMARY_6) = CLNG(NSUsers.CoEID);

 

[ByteMyzer]

You will need to employ in-line queries to accomplish this. Try something like:

SELECT *
FROM
 (SELECT CLng(CORP_PRIMARY_6) AS JoinField, *
  FROM CORP_CUST_DTL) AS CORP_CUST_DTL
INNER JOIN
 (SELECT CLng(CoEID) AS JoinField, *
  FROM NSUsers) AS NSUsers
ON CORP_CUST_DTL.JoinField = NSUsers.JoinField;

 

[Deniseobx]

Thank you, it works only that used VAL instead of CLONG