Type Mismatch in JOIN Expression

[DebbieF]

I am trying to link the account numbers together from two
tables, however, one table has the number as a text field
and one has the number as a number field. I cannot go
into the table to change the text field to a number field
because the table belongs to someone else and I am linked
to it. I know there is a way to link together fields from
two tables that have different types but I don't know how
to do it. How do I tell Access to recognize the joined
text field as a number field in the query?

 

[John Spencer (MVP)]

You can do this directly in a query, but you CANNOT use the query grid to build
the query.

In the query grid, assuming your fields are FieldA (text) and FieldB (number)

Build your query with your tables and leave out the join.
Switch the SQL view and you will see something like:

SELECT TableA.FieldA, TableB.FieldB
FROM TableA, TableB

Manually enter the join:
SELECT TableA.FieldA, TableB.FieldB
FROM TableA INNER JOIN TableB
ON Val(TableA.FieldA) = TableB.FieldB

You can also try the Conversion functions CLng, Csng, CDbl to convert the text
to a number. They don't handle nulls and text with non-numeric characters very
well.

 

[Guest]

Thank you very much. That did the trick.

-----Original Message-----
You can do this directly in a query, but you CANNOT use the query grid to build
the query.

In the query grid, assuming your fields are FieldA (text) and FieldB (number)

Build your query with your tables and leave out the join.
Switch the SQL view and you will see something like:

SELECT TableA.FieldA, TableB.FieldB
FROM TableA, TableB

Manually enter the join:
SELECT TableA.FieldA, TableB.FieldB
FROM TableA INNER JOIN TableB
ON Val(TableA.FieldA) = TableB.FieldB

You can also try the Conversion functions CLng, Csng, CDbl to convert the text
to a number. They don't handle nulls and text with non- numeric characters very
well.



.