joining on mis-matched data type

[EdStevens]

User is trying to produce a report joining two tables. Tables are linked
tables, using two Excel workbooks containing employee data. Join is on SSN.
In one workbook the SSN is (correctly) typed as a character string. In the
other it is typed as a number (double). Thus, not only are the data types
mismatched, but by defining SSN as a number, any leading zeros are dropped.
This particular worksheet comes to the user as a .csv file and investigation
of that file shows the SSN's that would have leading zeros have already been
truncated.

Somehow I need to build a query that will recast the 'numeric' SSN to a
character string AND lpad it with zeros. I can get around in Access, but am
far from fluent in it.

 

[Ken Snell [MVP]]

If WrongSSN is a variable that contains the SSN in a numeric datatype and
with no leading zeroes, this expression will change it to a string with
padded zeroes:

Format(WrongSSN, "000000000")

So, if you wanted to join two tables via the two SSN fields:

SELECT T1.*, T2.*
FROM T1 INNER JOIN T2
ON T1.GoodSSN = Format(T2.WrongSSN, "000000000");

 

[Wayne-I-M]

Hi

I am a bit confused by this. You have two tables that you want to link by
"some field". In one table you have a number and in the other you have text
with leading zeros. ???
You say the problem is th leading zeros. But this will not be a problem as
you can just convert the text field to a number - number can not have a
leading zero - this will get rid of the leading zeros and you can then link
them - or am I missing something here ??

eg
Table 1 (text)
000123

table 2 (number)
123

Convert table 1 to a number
123

Even if you convert table 1 field to text you will

 

[KARL DEWEY]

I need to build a query that will recast the 'numeric' SSN to a character
string AND lpad it with zeros.
Format([numeric_SSN], "000000000")

 

[John W. Vinson]

Hi

I am a bit confused by this. You have two tables that you want to link by
"some field". In one table you have a number and in the other you have text
with leading zeros. ???
You say the problem is th leading zeros. But this will not be a problem as
you can just convert the text field to a number - number can not have a
leading zero - this will get rid of the leading zeros and you can then link
them - or am I missing something here ??

Actually for an identifier like a SSN (or Tax ID, or drivers' license number,
etc. etc.) a Text datatype is better than any sort of Number datatype. You
will never do math with such a "number", and the text field will handle
leading zeroes.

 

[EdStevens]

Perfect. I knew it had to be a function like that, but not being familiar
with Access syntax and available functions, I just wasn't seeing it. Even
the on-line help which took me to MSDN library, I just wasn't recognizing
anything that seemed to work.

string AND lpad it with zeros.
Format([numeric_SSN], "000000000")

User is trying to produce a report joining two tables. Tables are linked
tables, using two Excel workbooks containing employee data. Join is on SSN.
In one workbook the SSN is (correctly) typed as a character string. In the
other it is typed as a number (double). Thus, not only are the data types
mismatched, but by defining SSN as a number, any leading zeros are dropped.
This particular worksheet comes to the user as a .csv file and investigation
of that file shows the SSN's that would have leading zeros have already been
truncated.

Somehow I need to build a query that will recast the 'numeric' SSN to a
character string AND lpad it with zeros. I can get around in Access, but am
far from fluent in it.