How to create a Join when fields are different

[Number11]

so i have 2 tables that i need to join within a query issues is that one table is set field as "Number" and the other table is set as a "Text", is their a way around as i cant change the table formats

 

[plog]

Using the table where it's a number create a query and create a calculated field to convert it to a string:

LinkField: Cstr(Your field)

Be sure to bring in all the other fields as well. Save that query then use it instead of the table it's based on, and JOIN it by the LinkField

 

[Isaac]

I would create a query that converts the one to number first (clng()) for example.
Then join to that query.

 

[The_Doc_Man]

As you can tell from the two responses, the trick includes the fact that you can JOIN queries as well as tables. Access just wants two recordsets to do the JOIN.

 

[isladogs]

There is another similar approach which cuts out the intermediate query.
Add both tables to the query designer but don't join them.
Now add a text field CStr(YourNumberField) and in the filter criteria enter the name of the text field from the other table

 

[CJ_London]

or you can use a non standard join

SELECT *
FROM tbl1 INNER JOIN tbl2 ON tbl1.numfield=val(tbl2.textfield)

If your table with the text field has a lot of records, It would be better to convert the text field to a number so that indexing can be applied or add it as a separate field.