Inner Join Error/Table field

[Adrienne]

I first tried to join the CAN Number fields in both tables.
The error below pop's up????

Type mismatch in expression.

Second:

I have two ODBC tables and I'm trying to perform a select
query so that the one transaction table (CANPV) can look
at the other table (CANLIST) and if the CAN Number in both
tables match then update the transaction table with the
other fields that exist on the CANLIST table.

I need your step by step help!!!!!

 

[Dan S.]

A lot of times, you'll get a type mismatch error if you
try joining two fields with different data types (i.e.,
one of your CAN Number fields is formatted as a number,
and one is formatted as text). Check the data type for
both of these fields in your two tables to see if that is
the problem; if so, just change one so that they both have
the same data type.

 

[Dan S.]

Okay, for the second part of your question--

You need to create a new query in design view. When it
prompts you which tables to show, choose your two tables.
Link the two tables by CAN Number (click on one, then drag
& drop it to the CAN number in the other table). Then,
pull down the fields in the transaction table that you
want to be updated. After that's done, go up to "Query" on
your toolbar, and change the query to an update query. You
can also click on the little drop-down box beside the
exclamation point, and choose "Update query". Once the
query is set as an update query, click in the "Update to"
field under your first field that you want updated. Right-
click and go to "Build". In the Expression Builder, double-
click "Tables", then double-click the CAN List table, and
choose the field that contains the data that you want that
field updated to, and hit "OK". Repeat these steps for
each of the fields that you want to update, then run the
query.

HTH,

-D.

 

[John Vinson]

I first tried to join the CAN Number fields in both tables.
The error below pop's up????

Type mismatch in expression.

It would appear that there is a type mismatch in the expression. What
are the Datatypes of the CAN fields in the two tables? They must match
- that is, if the CAN is an Integer in one table it must be an Integer
in the other, not a TEXT field.

Second:

I have two ODBC tables and I'm trying to perform a select
query so that the one transaction table (CANPV) can look
at the other table (CANLIST) and if the CAN Number in both
tables match then update the transaction table with the
other fields that exist on the CANLIST table.

I need your step by step help!!!!!

See above question. An Update query joining the two tables on CAN will
work IF the datatypes are identical and if there is a unique Index on
the field in the table you're trying to update.