Sort Last 4 Numbers of SSN

[rdw1260]

I'm using Access 2003. My organization is having a problem getting our
member’s SSN. I need to know if we only enter the last 4 numbers of a
member's SSN do we already have duplicates. I do not know how to setup a
query that will sort only the last 4 numbers.

When you have 2500 members, SSN's were how we kept track of our members,
especially females who changed their last names and do not let us know. The
USPS is replacing SSN with Employee Identification Numbers (EIN). In some
instances we get our member's complete SSN, the last 6 numbers, the last 4
numbers, their EIN, or a combination of part of the SSN and the entire EIN. I
have a form that will not save unless there is a SSN entered and the SSN is
not a duplicate. I have no idea how to get this form to do the same thing if
we do not have a complete SSN or we have an EIN or vice versa. Add to that if
we only have either the last 6 or 4 numbers of the member’s SSN. Any
suggestions how to possibly handle these types of situations?

Thanks,

 

[Allen Browne]

Create a query.

In the Field row in query design, type an expression like this:
Right([SSN],4)

Use this query as the source for your form/report, and you can match the
last 4 digits.

If you prefer, you could create a filter on your form based on the values
people enter into an unbound text box. If the unbound text box is named
txtSSNDigits, the filter would work like this:

If Not IsNull(Me.txtSSNDigits) Then
Me.Filter = "[SSN] Like ""*" & Me.txtSSNDigits & """"
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed).(donotspam)"

 

[Jamie Collins]

When you have 2500 members, SSN's were how we kept track of our members,
especially females who changed their last names and do not let us know. The
USPS is replacing SSN with Employee Identification Numbers (EIN). In some
instances we get our member's complete SSN, the last 6 numbers, the last 4
numbers, their EIN, or a combination of part of the SSN and the entire EIN. I
have a form that will not save unless there is a SSN entered and the SSN is
not a duplicate. I have no idea how to get this form to do the same thing if
we do not have a complete SSN or we have an EIN or vice versa. Add to that if
we only have either the last 6 or 4 numbers of the member's SSN. Any
suggestions how to possibly handle these types of situations?

Suggestions: use a stored proc (Access Query object with a PARAMETERS
clause) with NULL for their default values; use the Switch()
expression to apply your search criteria in order of preference (e.g.
exact matching EIN before partial SSN). Example using ANSI-92 Query
Mode syntax SQL DDL:

CREATE PROCEDURE GetMember
(
arg_EIN CHAR(8) = NULL,
arg_SSN_rightmost_characters VARCHAR(9) = NULL
)
AS
SELECT EIN, SSN
FROM Members
WHERE 1 = SWITCH
(
EIN = arg_EIN, 1,
RIGHT(SSN, IIF(arg_SSN_rightmost_characters IS NULL, 0,
LEN(arg_SSN_rightmost_characters)))
= arg_SSN_rightmost_characters, 1,
TRUE, 0
);

Perhaps you'd want to guard against returning all rows when
arg_SSN_rightmost_characters is the empty string <g> but hopefully
this should give you some ideas.

Jamie.

--