How do I compare social security numbers when one is numeric and .

[Guest]

I have two fields with social security data. The first one is stored as
numeric, and shows as 123456789. The second one is stored as text, and shows
as 123-45-6789. Regardless of the formatting, access is not recognizing that
the values in the two fields of the different tables are the same.

 

[MGFoster]

I have two fields with social security data. The first one is stored as
numeric, and shows as 123456789. The second one is stored as text, and shows
as 123-45-6789. Regardless of the formatting, access is not recognizing that
the values in the two fields of the different tables are the same.

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

123456789 doesn't equal "123-45-6789" 'cuz there are dashes in the text
version. You have to remove the dashes from one or add dashes to the
other. Since you can't enter dashes in a numeric column value you can
use a Replace() (Access 2000 & greater) to get rid of the text version
dashes. Do this on a copy of the original table if you don't want this
change to be permanent:

UPDATE table_name
SET SSN_text = Replace(SSN,"-","")
WHERE SSN_text Like "*-*"

Then change the data entry process so only one type of SSN is used - so
you don't have to do this again.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQdwrQIechKqOuFEgEQLDAwCgtEdTqSE807Lu8ilYyCgaTDKRB0cAoIzh
zSafCQ9zHxLfwtvIGbY31YDW
=Wjyk
-----END PGP SIGNATURE-----

 

[fredg]

I have two fields with social security data. The first one is stored as
numeric, and shows as 123456789. The second one is stored as text, and shows
as 123-45-6789. Regardless of the formatting, access is not recognizing that
the values in the two fields of the different tables are the same.

Well, if one field datatype in Number and the other is text, then the
values are not the same. 123-45-6789 is not the same as 123456789.
A Social Security Numbers should be text. You are never going to
perform mathematical operations with a SSN.

Change the Number field's datatype to text.
Run an Update Query to add the hyphens:
Update TableName Set TableName.SSN = Format([SSN],"@@@-@@-@@@@")

But then why 2 fields with the same SSN data?

 

[Guest]

Thanks for the help. I actually went back to the original excel file and did
a replace there. Then re-loaded the excel file into Access.
To answer your question, and that of the next post, the source data is given
to me each month. There are two sets of source data, each containing the SSN
as the only common field, that need to be compared to obtain the appropriate
analysis.
Thanks again for the replace idea.