Ignore Leading Zeros in Mixed Alpha-Numeric Text Fields Select Query

[excelCPA]

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?


For example:

Tbl1 Tbl2

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;

 

[Jeff Boyce]

If you were to use a query, add a new field, and in that field 'calculate' a
numeric equivalent of the zero-padded policy number, I believe you'd have a
non-zero-padded policy number. If you also converted THAT back to text, I
believe you'd have something to join on. You'd be joining between your
table with no-zeros policy numbers and the query.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[vanderghast]

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

 

[John Spencer]

One way might be to use a non-equi join. You can't build this type of join in
the query design view, but you can in SQL design view.

SELECT [Tbl1].[Policy #], Tbl2.PaymentReceived,
FROM [Tbl1] INNER JOIN Tbl2
ON [Tbl1].[Policy #] Like "*" & Tbl2.PolicyNum

Of course, that can lead to improper matches if you should have numbers like
W7540 and 07540 in table 1 and 7540 in table 2. Then 7540 would match both.

You could try joining as follows: The assumption is that the maximum length
of any policy number is 10 characters. If the maximum policy number length is
greater than 10 then you will need to modify the expressions used.


SELECT [Tbl1].[Policy #], Tbl2.PaymentReceived,
FROM [Tbl1] INNER JOIN Tbl2
ON Right("0000000000" & [Tbl1].[Policy #],10) =
Right("0000000000" & Tbl2.PolicyNum,10)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

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.

Whoever set this up - using two inconsistant, variable conventions for what
should be a unique identifier - deserves at LEAST forty lashes with a wet
noodle. If it was you, or if you have the power to fix it... do; if you're
stuck with some pointy-headed bosses bad decision, you have my sympathy!

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?


For example:

Tbl1 Tbl2

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;

I think you'll need a custom VBA function TrimZero:

Public Function TrimZero(strIN As String) As String
Dim iPos As Integer
For iPos = 1 to Len(strIn)
If Mid(strIn, iPos, 1) <> 0 Then
TrimZero = Mid(strIn, iPos)
Exit Function
End If
Next iPos
End Function

It'll be inefficient and probably slow (and almost certainly not updateable)
but you can then use a query

SELECT [Tbl1].[Policy #], Tbl2.PaymentReceived,
FROM [Tbl1] INNER JOIN Tbl2 ON TrimZero([Tbl1].[Policy #]) = Tbl2.PolicyNum;

 

[excelCPA]

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?

For example:

Tbl1            Tbl2

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 -

Thanks, the first suggestion worked great.