Getting "Type mismatch..." when querying two tables.

[Adam]

Hi all,

I have a problem whereby I am trying to join 2 tables. One field is a 16
digit text field, the other a "Long Integer".

I keep getting the "Type mismatch in expression" error.

Is there a way I can do this ? I'm accessing the tables via odbc.

SELECT qryNOTES_Parcels_Notes.*, RMPCL.*
FROM qryNOTES_Parcels_Notes INNER JOIN RMPCL ON
qryNOTES_Parcels_Notes.ACCOUNT = RMPCL.KY;

the field ACCOUNT is the 16 digit text field. The field KY is Long Integer.

any help would be appreciated,

Adam

 

[Ken Snell]

To join two fields they must be of the same data type. In your case, one is
a text format and the other is a long integer format.

 

[Adam]

Is there any way around this ? I really need to be able to do this.

cheers,

Adam

 

[Ken Snell]

Import the one table's data into a temp table that has the correct format
for the linking fields.

Other than that, modify the table's structure so that the field's format is
the same.

I don't know of a way to "wrap" the joining field with a function that will
change the format.

 

[Duane Hookom]

Try this. I doubt it will be editable.

SELECT qryNOTES_Parcels_Notes.*, RMPCL.*
FROM qryNOTES_Parcels_Notes, RMPCL
WHERE Clng(qryNOTES_Parcels_Notes.ACCOUNT) = RMPCL.KY;

 

[Ken Snell]

Duane -

I had tried a similar thing in a sample query that I set up, only I used
CStr to wrap the "long integer" field, and it didn't work for me (A2K2). But
I just tested my sample with using CLng as the wrapper for the "text" field,
and it works.

Wonder what the difference is?

One other thing to watch for....if the RMPCL.KY field can contain a Null
value, you'll get an error from the attempt to convert the text field to
Long integer, and then the query will return #Name values in all fields in
all the records.

 

[Duane Hookom]

I don't see any difference. I thought maybe one or the other return a
leading or trailing space but neither does.

 

[Adam]

Thanks guy,

really appreciated it !!

cheers,

Adam

 

[Ken Snell]

Hmmmm.....maybe the "error" was being caused by some null fields in my
sample.

OK - thanks Duane...I've learned another thing tonite!