Query Mismatch Expression Error?!?

[chaos18]

I used the query written below (created with the help of a MS Access programmer named bill) and it works on my other database with different table names (Microfilmlibrary1 & main1 are the table names used that create the conflict). I've tried so many different avenues but every time i run any variation of this query it gives me the same error 'Type mismatch in expression.' Any idea's on what i might be overlooking or doing wrong?



UPDATE microfilmlibrary1 INNER JOIN main1 ON ([microfilmlibrary1].[Doc_Type]=[main1].[Doc_Type]) AND ([microfilmlibrary1].[Book]=[main1].[Book]) SET main1.Source_ID = [microfilm1].[Source];

 

[shannonp]

The UPDATE query looks fine, the only thing I would have to ponder is what type of field [Main1].[Source] is? If its an 'Autonumber' then you MUST declare [microfilmlibrary1].[Source ID] as a 'Long Integer' (not 'Integer').

If its not an autonumber then I can only suggest you check both '[Main1].[Source]' and '[microfilmlibrary1].[Source ID]' and check if there field types are identical (or that at least '[microfilmlibrary1].[Source ID]' is of type 'Text', which can take on almost any value you throw at it.

Hope this helps

 

[chaos18]

Thanks for the tip and i think i know what you are saying but i already made sure the settings are the same...the only thing i could think that might cause a problem is the fact that the main1 table is being updated but since it already has information in it, it is cause a conflict. I know it shouldnt be but other than that i can't think of anything.
Previously i thought it was inconsistancies in the two tables...for example the main1 table has book and Doc_type in common but the rest of the fields are different or have slightly different names, ie Source compared to Source_ID. i have partually ruled that out because i went back and renamed and added columns to each of the tables so that they were 'exactly' the same in the way of names and function(by function i mean operation, such as Text, autonumber, number and so on).

thanks again for the advice and if you have any more its always welcome


MaTT Gunter

 

[shannonp]

What I have done in the past is make a copy of a query and delete ONE field at a time then re-running the query for each deletion...repeating this process until you either have no fields left to delete or until the problem dissappears. If the problem suddenly dissapears then at least you'll know that the culprit is the last field you deleted.

If you're still having problems, and your db is small enough (like less than 2MB) send it to me and I'll look.