Convert Text Field to Integer and do JOIN in he same Query

[AccessHunter]

Please help....

I am trying to JOIN two tables, Table1 and Table2,
the fields used for the JOIN are Table1.CaseNum (Number field) and Table2.Casetxt (Text field).
I know it will not let me do the JOIN between Number field and Text field and for that I should have a query to do the Text to Integer conversion before I try the JOIN Query.

I would like to know if the following is possible
I want to do 2 things in the same Query,
1) convert the Text field to Integer and then
2) JOIN Table1.CaseNum and Table2.CaseTxt(converted to Integer)

Thanks in advance.

[Rabbit]

It might work if you do the conversion using a subquery in the FROM clause rather than in the main query.

[AccessHunter]

Thanks for your reply.

In the query I gave the criteria for Table1.CaseNum as, =Int([Table2.CaseTxt]), and it worked. It did the conversion and the JOIN without actually drawing a line to join the 2 tables.

Thanks again for your reply.

[Rabbit]

Good luck.