Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Unanswered: Query for errors

    I have a social security number field and I would like to run a query to check for errors. I need to make sure that all 9 characters are entered(no template characters). In the past, I've seen numbers entered incorrectly.
    For example, missing the first 3 or last 4 numbers or missing numbers in the middle of the SSN. These records are scanned into an Access 2000 database and the scanning software requires complicated code for that type of validation. So I'm looking for a simple approach. Any idea?
    Thanks!!!

  2. #2
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    try a where clause like this, maybe..?

    WHERE
    len(cstr(ssn)) <> 9 OR
    asc(mid(cstr(ssn),1,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),2,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),3,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),4,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),5,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),6,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),7,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),8,1)) NOT BETWEEN 48 AND 57 OR
    asc(mid(cstr(ssn),9,1)) NOT BETWEEN 48 AND 57

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •