Data Type Mismatch

[Chris]

I have an account identifier that is identified as text in one table and
number in another. When I run a query to pull data from both tables using
the account identifier as the join column, I get a data type mismatch error.
If I try to change the property for the field in the table, I get a message
that there isn't enouhg disk space on the memory page. Is there an easier
way to join two tables on fields with different data types?

 

[Golfinray]

As a rule when you have a data type mismatch you won't solve the problem
without changing one of them. If you can't change one of them link them on
something else, like an autonumber field. You may still have problems even
then.

 

[John W. Vinson]

I have an account identifier that is identified as text in one table and
number in another. When I run a query to pull data from both tables using
the account identifier as the join column, I get a data type mismatch error.
If I try to change the property for the field in the table, I get a message
that there isn't enouhg disk space on the memory page. Is there an easier
way to join two tables on fields with different data types?

As Golfinray says, you must resolve the mismatch. Ideally (for efficiency, to
allow relationships to be defined, etc.) you should have the two tables with
the same datatype for the joining field. For an account identifier I'd
strongly suggest using Text datatype, since you'll never be doing math with
the account numbers.

The memory error arises when you have a largish table and try to change a
datatype; Access will try to keep the entire table in memory *TWICE*, the old
table and the new one. To get around the problem, create a new table, empty,
with the datatype you desire; then run an Append query to migrate the data
from the old table into the new one. Reestablish (or establish!)
relationships, test everything out, then delete the old table and use Tools...
Database Utilities... Compact and Repair to recover the space that it
occupied.

 

[George Nicholson]

You can try creating an intermediate query that changes text to number (or
vice versa) and then use that 'conversion' query in your join.

TextIDFieldasLong: Clng([TextIDField])
or
NumericIDFieldasString: Cstr([NumericIDField])

 

[keith]

You could also add a new indexed field to one of the tables with the data
type that matches the second table. If one of the tables is an account
reference table, this would be an ideal place as the only time the new field
would have to be populated is when a new account is added. As an alternative,
you could then use an update query to update this new field.

You can try creating an intermediate query that changes text to number (or
vice versa) and then use that 'conversion' query in your join.

TextIDFieldasLong: Clng([TextIDField])
or
NumericIDFieldasString: Cstr([NumericIDField])