Query SSNs to ensure all have 9 digits

[Jake Forrey]

Hi there,

I'm sure there is a VERY simple solution to this, but since I'm fairly new to Access, this simple task is proving to be somewhat confusing.

I have a Table containing thousands of names and SSNs (among other data). I'd like to run a query that will allow me to see which records have incomplete SSNs (i.e. 8 digits instead of the full 9).

Any thoughts? Thanks!

[Rabbit]

The Len() function will return the length of string input.

[NeoPa]

Using Len(), create a field in a query that displays both the ID of the record, as well as the length of the SSN field. In the Criteria row enter <> 9. When run, this will show all records where the length <> 9.

[ADezii]

The <> 9 Rule applies 'only' if the Symbols (-) are 'not' stored with the SSAN. If they are, all Records would be returned by the <> 9 Criteria.

[NeoPa]

I've no idea what an SSN refers to here ADezii, but as it wasn't explained in the question I should be able to assume it's not relevant. The question implies that the data's numeric, so if it's not then the GIGO law comes into play.

[Rabbit]

SSN is an American government thing. It's a social security number used to uniquely identify an individual. It's required to get a job.

A common way of writing the SSN is ###-##-####

As a side note, SSNs should be considered confidential information so I hope you've encrypted your data.

[NeoPa]

Thanks for that explanation Rabbit.

I still feel that the OP has an answer to their question though. If they haven't asked the question properly then the answer may not suit them very well.