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.

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Two suggestions:

1. UPDATE table1 so the column has leading zeros:

UPDATE table1 SET line_number = Format(line_number,"000")

Be sure to use the correct number of zeros in the Format() function.

2. Use Val() in the JOIN:

...table1 As T1 INNER JOIN table2 As T2 ON Val(T1.line_number) =
Val(T2.line_number)...

This will slow down the query 'cuz all the rows in the tables will
have to be scanned in order to evaluate the Val() functions.

3. JOIN on different columns that uniquely identify the rows (records)
in each table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbGnioechKqOuFEgEQLVWgCgqq69pekt9W7CFeDeRfS0PiCiYC0AoLGT
L4J5FjY4GkfRsAMPkpJ8aVKO
=PjR9
-----END PGP SIGNATURE-----