The ON clause can involve complex expressions, as long as the final result

is Boolean (or null). The following should work, if the are no null:

SELECT Tbl1.[Policy #], Tbl2.PaymentReceived,

FROM Tbl1 INNER JOIN Tbl2

ON tbl1.[Policy #] = tbl2.[Policy #]

OR (IsNumeric(tbl1.[Policy #])

AND IsNumeric(tbl2.[Policy #])

AND val(tbl1.[Policy #]) =val(tbl2.[Policy #]) )

or,

SELECT Tbl1.[Policy #], Tbl2.PaymentReceived,

FROM Tbl1 INNER JOIN Tbl2

ON RIGHT( STRING(50, "0") & tbl1.[Policy #] , 50)

= RIGHT( STRING(50, "0") & tbl2.[Policy #] , 50)

assuming that the Policy # fields have a limit of 50 characters.

The first solution follows your instructions, while the second simply pad

everything with "0", so that every one has exactly 50 characters. That

second solution is probably slower, though.

Vanderghast, Access MVP

I have two tables, both contain data by policy numbers. Some policy

numbers are entirely numeric some contain alpha characters. The first

table contains leading zeros for numeric policy numbers and the number

of leading zeros is not fixed, it varies by policy. The second table

has no leading zeros for numeric policy numbers. Both are linked

tables and are "text" type fields.

How do I make a join in a select query that will ignore leading zeros

for policies that are all numeric, while still allowing for polices

that contain alpha characters?

Policy # PolicyNum PaymentReceived

0001234 1234 100

02345 3456 80

W78QR7 W78QR7 200

Below is my select query:

SELECT [Tbl1].[Policy #], Tbl2.PaymentReceived,

FROM [Tbl1] INNER JOIN Tbl2 ON [Tbl1].[Policy #] = Tbl2.PolicyNum;- Hide quoted text -

- Show quoted text -