By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Query SSNs to ensure all have 9 digits

P: 1
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!
Feb 24 '12 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,315
The Len() function will return the length of string input.
Feb 24 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 24 '12 #3

ADezii
Expert 5K+
P: 8,597
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.
Feb 24 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 24 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
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.
Feb 24 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 24 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.