Join in query with leading zero

[JPS]

I am creating a query with tables received from a third party. I need to
join the tables on a field called Line_Number.

The field is defined as text in both tables.

However, one table has it with leading zeroes and the other does not

For example, Table1 displays 1 and Table2 displays 001

The join does not work in this situation.

 

[KARL DEWEY]

A couple of alternatives --
Create queries and add calculated field LineNumber: Val([Line_Number]) for
each.
Create queries and add calculated field LineNumber:
Right("0000"&[Line_Number], 4) for each.

Join the two in design view, switch to SQL view and edit join to do either
of the above. NOTE - You will not be able to view the query in design view
after doing this.