Ignoring leading zeros in comparison

[Guest]

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.

 

[Allen Browne]

Use CLng() or Val() around the field names in the FROM clause of your query.

Switch the query to SQL View (View menu.)
You will see something like this:
SELECT Field1, Field2
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field2

Change the FROM clause like this:
FROM Table1 INNER JOIN Table2 ON Val(Table1.Field1) = Val(Table2.Field2)

You may not be able to switch back to design view after this change.

 

[Guest]

Allen,

Thanks for the quick reply, I had to modify my query and join a third field,
but I think the VAL function you sugested should still work, however I am
having trauble I get a data type mismatch error. The following is my query,
the name of the two fields that I am havig trouble joining are:
CORP_PRIMARY_6 from the table "CORP_CUST_DTL" and
CoEID from the table "accessUsers".


SELECT DISTINCT CORP_CUST_DTL.CORP_PRIMARY_6, accessUsers.CoEID,
accessUsers.Company
FROM CORP_CUST INNER JOIN (CORP_CUST_DTL LEFT JOIN accessUsers ON VAL (
CORP_CUST_DTL.CORP_PRIMARY_6) = VAL (accessUsers.CoEID ) ) ON
CORP_CUST.CORP_CUST_ID = CORP_CUST_DTL.CORP_CUST_ID;


Please let me know if I am overseeing anything

 

[Allen Browne]

Is there any chance that these fields could contain a Null?
Val() doesn't cope with Null, and you do have that problem with the outer
join.

You might try working with just the tables CORP_CUST_DTL and accessUsers
until you get it working. If necessary, save that query, and build another
one into it.

 

[Guest]

Hi Sara,

I would create query for the table that has the field with the leading
zero's, and remove them

Select TableName.* , CStr(Val(Nz([FieldName],0))) As NewFieldName From
TableName

Then, in another query combine the other table with the new query using the
new field.
I prefer that way, beause with the other mothod you can't view the query in
design view, just SQL view.