"Type Mismatch" in Access query 2

[newguy86]

Hi All,
I'm trying to combine data from two tables and I cannot get around this type mismatch error. I have an assignments table that has an AssignID field which is setup as an autonumber and I have a ChangeLog table that has a recID field that stores the record ID that was changed and that is setup as a text field. I cannot change the recID field to a number data type because there isn't always a number that's put into that field so I'm kind of stuck on what my options are.

Is there anything else that I can do to be able to join these two tables by those fields?

Any assistance would be greatly appreciated!

Travis

http://www.apexsystemsinc.com

 

[Andrzejek]

If one field is a number, and another one is text, and you cannot convert text to a number, you may want to convert number to text 'on-the'fly' in your SQL

Try:

...
WHERE Format$(Assignments.AssignID) = ChangeLog.recID

Have fun.

---- Andy

 

[newguy86]

Thank you Andrzejek!

Your suggestions works great but everytime I open the query for design it deletes the join because Access 2007 doesn't have a way to represent that expression.

Is there a way to do this that's more stable?

Travis

http://www.apexsystemsinc.com

 

[Shawn12]

In the criteria section under where you have the recID put CSTR(Assignments.AssignID) in there..that is how you join unlike field types. You dont need a physical line from table to table in design view. Hope that helps.

 

[newguy86]

That worked!

Thanks

Travis

http://www.apexsystemsinc.com