Filtering Social Security # Problem

[AB7780]

Hello,

I have a database, where one column is Social Security numbers. Some records have just a "0", some have the last 4 digits "3214" or some have 9 digits.

Is there any way I can filter to just show the records with the 9 digit SS# number, and not show others?

I apologize if this is a dumb question. Usually the data I deal with can be handled in Excel, but this set is large, forcing me to struggle with Access.

Thanks so much!
Andrew

 

[Cbrine]

Two options I can think of.
Easy Option 1
Just open the table and do a sort on that SSN field, then roll the screen down until you get to the valid SSN and highlight from there to the end of the table. Copy and paste to Excel?

Harder Option 2
You will need to setup a query.
Build a custom Field like this.

Test:iif(len([SSN#]=9,"Yes","")

Criteria for Test:
"Yes"

PS-If your SSN# has spaces, you will need to change the 9 to whatever the lenght should be.
Our format is
### ### ###
I'm not sure about yours.

 

[mdmilner]

You can use len in a parameter to limit the results of a query.

Mike

SELECT fld1, fld2, fld3
FROM tblName
WHERE Len([fld1])=9;

 

[giacomo]

you can also put Len([fld1])=9 right into your form filter on your table instead of building a query