Joining 2 Tables

[jeffc]

Hello! I need to join 2 tables, 1 table with all of the fields, and another with just 2.

The link between the 2 tables is an ID field in field 2 wich is long integer.

The problem is, the ID field in table 1 that correlates to the ID field in table 2, is in the datatype text.

I cant change the datatype of the field in table 1 because it is a linked table to a txt file. And some of the values in there are dashes, indicating no ID. (not sure why).

So, is there ANY way to link the2 tables with the 2 different datatypes

Thanks in advance!

 

[Dennisk]

In your sql Statement you can convert the field used to link the two tables. Convert the text (I assume they are numbers) to integer.

ex JOIN epi ON mpi.Internal_ID = Cint(epi.Internal_ID)

 

[boblarson]

Create a query with all fields included on table 2 and include this as a field:

MyIDAsNumber:CLng([YourIDFieldNameHere])

Then you can link on that field using Table 1 and your MyIDAsNumber field.

 

[boblarson]

Dennisk's version is more direct. I'm not a very good SQL person so I think in terms of the QBE grid and all. So, his is simpler.

 

[jeffc]

Oooo...the SQL modification looks liek a good idea. But still new to the SQL.

Here is the SQL that links the 2 databses:

JOIN tblTPs ON tblCPs.[Time Profile ID] = tblTPs.ID

the tblCPs.[Time Profile ID] is the one that is text...so do i do this:

cint(tblCPs.[Time Profile ID])?

 

[jeffc]

ha, i got it! I used your example and just made it CSTR() thank you!!!