Joining on a Field with Leading Zeros

[Guest]

I am trying to run a query from a table created in Access 2000 and a linked table created in Access 97. We cannot change the data or design of anything in the Access 97 database. The Access 97 table has the leading zeros (text field). The account numbers we have provided from another source do not have leading zeros (text field also, defined the same), and will not allow us to join on those two fields. Do you know of a process that would trim off leading zeros? I was thinking of a query that brings in the data from the Access 97 table and trims off the leading zeros. Is there such a thing?

 

[Douglas J. Steele]

It's probably easier to add leading zeros to the 2nd set of numbers.

Let's say you want 10 digit account numbers. You'd use Right("0000000000" &
[NoLeadingZeroNumber], 10)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am trying to run a query from a table created in Access 2000 and a

linked table created in Access 97. We cannot change the data or design of
anything in the Access 97 database. The Access 97 table has the leading
zeros (text field). The account numbers we have provided from another
source do not have leading zeros (text field also, defined the same), and
will not allow us to join on those two fields. Do you know of a process
that would trim off leading zeros? I was thinking of a query that brings in
the data from the Access 97 table and trims off the leading zeros. Is there
such a thing?

 

[Bruce M. Thompson]

I am trying to run a query from a table created in Access 2000 and a linked
table created in Access 97. We cannot change the data or design of anything in
the Access 97 database. The Access 97 table has the leading zeros (text field).
The account numbers we have provided from another source do not have leading
zeros (text field also, defined the same), and will not allow us to join on
those two fields. Do you know of a process that would trim off leading zeros?
I was thinking of a query that brings in the data from the Access 97 table and
trims off the leading zeros. Is there such a thing?

Back up your database before doing anything, but I suspect that simply changing
(in table design view) the field with the leading zeros from a Text field to
Number, Long Integer (saving the changes) and back to Text (again, saving the
changes) will take care of that for you. What I'm unsure of, however, is whether
a large number of records in that table will cause a problem with the automatic
conversion. If that table has only a small to moderate number of records, you
shouldn't have any problems.

 

[Bruce M. Thompson]

I am trying to run a query from a table created in Access 2000 and a linked

table created in Access 97. We cannot change the data or design of anything in
the Access 97 database. The Access 97 table has the leading zeros (text field).
The account numbers we have provided from another source do not have leading
zeros (text field also, defined the same), and will not allow us to join on
those two fields. Do you know of a process that would trim off leading zeros?
I was thinking of a query that brings in the data from the Access 97 table and
trims off the leading zeros. Is there such a thing?

After re-reading your post, I see that my suggestion technically violates the
"cannot change the data or design of anything", so I should have, at least,
suggested that you add a joinable column to your query to:

1) Provide a column based on the value of the field with the leading zeros -
using Val([FieldName]) - to allow joining to the field in the other table with
no leading zeros, or

2) Provide a column based on the field in the table with no leading zeros,
formatted to include the leading zeros - using Format([FieldName],
"0000000000") - using the appropriate number of zeros in the format to match the
length of the entries in the field that contains the leading zeros. Of course,
this would only work if the length of all the entries in that field are of the
same length.

 

[John Spencer (MVP)]

You can't do this in the design grid, but you can do it in the SQL.

SELECT <<Your Field List>>
FROM Access2000Table as A INNER JOIN Access97Table as B
ON A.AccountNumber = Val(B.AccountNumber)

OR
SELECT <<Your Field List>>
FROM Access2000Table as A INNER JOIN Access97Table as B
ON Format(A.AccountNumber,"000000000") = B.AccountNumber

The format of the A.AccountNumber should be set to the same number of characters
as in the B.AccountNumber.