Use make table query and changed linked field property

  • Thread starter Thread starter Lurch
  • Start date Start date
L

Lurch

Hi There!
Using Access 2000 on windows pro 2000. I have two linked tables (I
cannot change field properties for various reasons). I have placed
these two tables into a make table query but the joined fields are
incompatible. One is an autonumber and the other is text. Because of
this I get a "Type mismatch" error.

How can I change the property of the autonumbered field to text. Then
I can use another query to make the table I require.

Any help would be great!

Thanks!

Tony
 
You can't change an autonumber to a text.

You may want to import the table rather than link it, with
an imported table you may change the properties.
 
Surround the Autonumber with CStr function or the format function. Either will
convert the autonumber value in the query to a string.

Sorry, i can't give you more detail, but I don't exactly know what your tables
and fields look like.

SELECT *
FROM TableA Inner Join TableB
ON TableA.StringField = CStr(TableB.AutoNumberField)

You cannot do this in the query grid, but you can build all the query except the
join and then switch to the SQL view and do the join there.

Another method would be to use two queries. Query one would just add a
calculated column to your autonumber table

SELECT *, CStr(AutonumberField) as txtAuto
FROM TableB

Then, you could put this query and the original table into a query and join the
tableA stringfield to the new txtAuto field.

This can be slow, since there won't be any indexes available on the calculated field.
 
Back
Top