How do I display part of a SSN on a report?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerrel Baxter - 05 Jan 2006 20:06 GMT

We produce a variety of reports for clients which contain SSN's. I need to
be able to display only part of the SSN field on reports.

Reply to this Message

Jerry Whittle - 05 Jan 2006 20:17 GMT

Which part? That little bit of info will make all the difference.
Also are the SSANs stored with dashes ( - ) or not?

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> We produce a variety of reports for clients which contain SSN's. I need to
> be able to display only part of the SSN field on reports.

Reply to this Message

Jerrel Baxter - 05 Jan 2006 20:41 GMT

My current goal is to selectively allow display of either all of the SSN
field (11 characters including dashes) or the last four characters only. A
hint on another posting fixed an error where I was attempting to use
=Right([SSN],4) for the control source without changing the output field name
from SSN. I'm now using PartSSN for the field name and the above specified
formula for the output display and getting the output I need. I would like
to specify the field width value in the function programmatically as either 4
or 11 based on content of a field on a request form but am unsure how to do
this.

I also have a form which runs the report which allows suppression of the
field entirely by setting Me.PartSSN.Visible = True or False appropriately in
a subroutine attached to the ReportHeader Format based on field content in
the request form. This is dependent on an event procedure so I don't think
this method will work with the field specification unless I can
programmatically change the =Right([SSN],4) specification for the Control
Source property. Any ideas?

> Which part? That little bit of info will make all the difference.
> Also are the SSANs stored with dashes ( - ) or not?
>
> > We produce a variety of reports for clients which contain SSN's. I need to
> > be able to display only part of the SSN field on reports.

Reply to this Message

Wayne Morgan - 05 Jan 2006 20:20 GMT

Make the textbox displaying the SSN a calculated textbox.

Example Control Source:
=Right([SSNField], 4)
or
="***-**-" & Right([SSNField], 4)

For this to work, the textbox can not have the same name as the field. So,
if the field is named SSNField, name the textbox txtSSNField.

Signature

Wayne Morgan
MS Access MVP

> We produce a variety of reports for clients which contain SSN's. I need
> to
> be able to display only part of the SSN field on reports.

Reply to this Message

Jerrel Baxter - 05 Jan 2006 21:13 GMT

I now have the display of a part of the SSN field enabled but need to be able
to display either all or part based on a requesting form's content field.
Thanks for the example showing catenation of the "***-**-" string. That was
very helpful and your comment regarding changing the field name of the
textbox was also helpfule.

Is there a way to programmatically change the Control Source content to
allow selective display of either all or part of the SSN? A comment I
attached on another reply may be helpful. I have tried setting
PartSSN.ControlSource to another value but receive a message from the
run-time system that this change cannot occur at run-time. Any ideas on
where it could be done on a programmatic basis?

> Make the textbox displaying the SSN a calculated textbox.
>
[quoted text clipped - 9 lines]
> > to
> > be able to display only part of the SSN field on reports.

Reply to this Message

Wayne Morgan - 05 Jan 2006 21:40 GMT

Yes, this can be done at run time. The following assumes a checkbox on the
requesting form. True (checked) will show a partial SSN, False will show the
full SSN.

Example:
=IIf([Forms]![frmReqForm]![chkPartialSSN], "***-**-" & Right([SSNField], 4),
[SSNField])

Signature

Wayne Morgan
MS Access MVP

>I now have the display of a part of the SSN field enabled but need to be
>able
[quoted text clipped - 26 lines]
>> > to
>> > be able to display only part of the SSN field on reports.

Reply to this Message

Jerrel Baxter - 05 Jan 2006 23:55 GMT

Thanks! This should allow me to do everything that I need.

Reply to this Message