trim leading zeroes / change data type

[CherylH]

Hi all,
I've got 2 questions that I need help with.

I have 2 linked tables that contain a large amt of data in each. The field
that I need to join on is set up differently on one table than the other.
Also, the field on one table contains leading zeroes, where the other does
not.

Tbl 1 --> primary_id - data type is long integer
Tbl 2 --> primary_id - data type is text and the values have leading zeroes

IS there something I can place in the query grid for Tbl 2.primary_id in
order for the join to work properly and not receive a data mismatch?

Unfortunately I do not have the authority to change the design of either of
these tables, and cannot actually import them b/c of their size.

Any help would surely be appreciated. Thanks!
Cheryl

 

[KARL DEWEY]

Yep.
Long_Integer_Key: Val([primary_id])

 

[John Spencer]

You can handle this in SQL View. Build the query as you normally would
(ignore the data mismatch) then Open the query in SQL view and find the join

FROM [tbl 1] INNER JOIN [tbl 2]
ON [tbl 1].PrimaryID = [tbl 2].Primary_Id

Change that to the following (Clng will convert the text string to a
number). This works as long as the text string has a number value and
is never null.
FROM [tbl 1] INNER JOIN [tbl 2]
ON [tbl 1].PrimaryID = CLng([tbl 2].Primary_Id)

Or you can use the val function and concatenate a zero to the beginning
to handle nulls and strings that cannot be converted to numbers (they
will become zero).

FROM [tbl 1] INNER JOIN [tbl 2]
ON [tbl 1].PrimaryID = Val("0" & [tbl 2].Primary_Id & "")


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================