Blocking SSN numbers in Reports

[Built-in functions]

I have an Access Report where I would like only the last 4-digits of the SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Rick B]

What do you mean, there is more than one? Each field in a database should
contain one piece of data.

If you have three dependents, then there should be three SS#s. Since this
is a one-to many relationship, these would most likely be stored in a
separate related table with one record for each dependant. If a person has
2 dependants, they'd have two records in the related table. If they have 5
dependants, they'd have five records.

 

[Duane Hookom]

"there is more than one" what? Are you storing multiple values in a single
field?

 

[Pat Hartman\(MVP\)]

If you are asking the question, you are storing the dependent's information
improperly. Each dependent should be stored in a single record so there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would need to
separate the entries, apply the Right() function to each individual SSN, and
then concatenate them again for display. There are no shortcuts for bad
table design.

If you are in a position to change the design, now would be a good time.
You would add a small subform to the mainform to input dependent's first
name, last name (remember they may be different), and SSN.

 

[Toniahb]

My apologies to all, I wasnt clear enough. The dependents are listed
individually in te detailed section. When I apply the right function I get
an error code. I am guessing the reason for this is because there are
multiple SSNs for multiple dependents. When there is just one SSN to work
with like (myssn and spousessn) the right function works fine. I need a code
that will now that there are more than one dependent listed and can block the
first 5 digits of the ssn.

If you are asking the question, you are storing the dependent's information
improperly. Each dependent should be stored in a single record so there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would need to
separate the entries, apply the Right() function to each individual SSN, and
then concatenate them again for display. There are no shortcuts for bad
table design.

If you are in a position to change the design, now would be a good time.
You would add a small subform to the mainform to input dependent's first
name, last name (remember they may be different), and SSN.

I have an Access Report where I would like only the last 4-digits of the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Duane Hookom]

Can you share some table structures? Do you realize that a text box using an
expression like =Right([myfield],4) can't be named "myfield"?

--
Duane Hookom
MS Access MVP
--

My apologies to all, I wasnt clear enough. The dependents are listed
individually in te detailed section. When I apply the right function I
get
an error code. I am guessing the reason for this is because there are
multiple SSNs for multiple dependents. When there is just one SSN to work
with like (myssn and spousessn) the right function works fine. I need a
code
that will now that there are more than one dependent listed and can block
the
first 5 digits of the ssn.

If you are asking the question, you are storing the dependent's
information
improperly. Each dependent should be stored in a single record so there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would need
to
separate the entries, apply the Right() function to each individual SSN,
and
then concatenate them again for display. There are no shortcuts for bad
table design.

If you are in a position to change the design, now would be a good time.
You would add a small subform to the mainform to input dependent's first
name, last name (remember they may be different), and SSN.

in
message news:[email protected]...

I have an Access Report where I would like only the last 4-digits of the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Toniahb]

I the header there are text controls that list the primary account holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can have
more than one child. Listed in the detailed section there is only 1 text box
for dependents and their SSN, when the report is generated the SSN appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

"there is more than one" what? Are you storing multiple values in a single
field?

--
Duane Hookom
MS Access MVP
--

I have an Access Report where I would like only the last 4-digits of the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Toniahb]

I the header there are text controls that list the primary account holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can have
more than one child. Listed in the detailed section there is only 1 text box
for dependents and their SSN, when the report is generated the SSN appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

Can you share some table structures? Do you realize that a text box using an
expression like =Right([myfield],4) can't be named "myfield"?

--
Duane Hookom
MS Access MVP
--

My apologies to all, I wasnt clear enough. The dependents are listed
individually in te detailed section. When I apply the right function I
get
an error code. I am guessing the reason for this is because there are
multiple SSNs for multiple dependents. When there is just one SSN to work
with like (myssn and spousessn) the right function works fine. I need a
code
that will now that there are more than one dependent listed and can block
the
first 5 digits of the ssn.

If you are asking the question, you are storing the dependent's
information
improperly. Each dependent should be stored in a single record so there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would need
to
separate the entries, apply the Right() function to each individual SSN,
and
then concatenate them again for display. There are no shortcuts for bad
table design.

If you are in a position to change the design, now would be a good time.
You would add a small subform to the mainform to input dependent's first
name, last name (remember they may be different), and SSN.

in
message I have an Access Report where I would like only the last 4-digits of the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Toniahb]

I the header there are text controls that list the primary account holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can have
more than one child. Listed in the detailed section there is only 1 text box
for dependents and their SSN, when the report is generated the SSN appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

My apologies to all, I wasnt clear enough. The dependents are listed
individually in te detailed section. When I apply the right function I get
an error code. I am guessing the reason for this is because there are
multiple SSNs for multiple dependents. When there is just one SSN to work
with like (myssn and spousessn) the right function works fine. I need a code
that will now that there are more than one dependent listed and can block the
first 5 digits of the ssn.

If you are asking the question, you are storing the dependent's information
improperly. Each dependent should be stored in a single record so there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would need to
separate the entries, apply the Right() function to each individual SSN, and
then concatenate them again for display. There are no shortcuts for bad
table design.

If you are in a position to change the design, now would be a good time.
You would add a small subform to the mainform to input dependent's first
name, last name (remember they may be different), and SSN.

I have an Access Report where I would like only the last 4-digits of the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Toniahb]

I the header there are text controls that list the primary account holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can have
more than one child. Listed in the detailed section there is only 1 text box
for dependents and their SSN, when the report is generated the SSN appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

What do you mean, there is more than one? Each field in a database should
contain one piece of data.

If you have three dependents, then there should be three SS#s. Since this
is a one-to many relationship, these would most likely be stored in a
separate related table with one record for each dependant. If a person has
2 dependants, they'd have two records in the related table. If they have 5
dependants, they'd have five records.

--
Rick B

I have an Access Report where I would like only the last 4-digits of the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Duane Hookom]

Again:
"Can you share some table structures? Do you realize that a text box
using an expression like =Right([myfield],4) can't be named "myfield"?"

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)

--
Duane Hookom
MS Access MVP
--

I the header there are text controls that list the primary account holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can have
more than one child. Listed in the detailed section there is only 1 text
box
for dependents and their SSN, when the report is generated the SSN appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

Can you share some table structures? Do you realize that a text box using
an
expression like =Right([myfield],4) can't be named "myfield"?

--
Duane Hookom
MS Access MVP
--

My apologies to all, I wasnt clear enough. The dependents are listed
individually in te detailed section. When I apply the right function I
get
an error code. I am guessing the reason for this is because there are
multiple SSNs for multiple dependents. When there is just one SSN to
work
with like (myssn and spousessn) the right function works fine. I need
a
code
that will now that there are more than one dependent listed and can
block
the
first 5 digits of the ssn.

:

If you are asking the question, you are storing the dependent's
information
improperly. Each dependent should be stored in a single record so
there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would
need
to
separate the entries, apply the Right() function to each individual
SSN,
and
then concatenate them again for display. There are no shortcuts for
bad
table design.

If you are in a position to change the design, now would be a good
time.
You would add a small subform to the mainform to input dependent's
first
name, last name (remember they may be different), and SSN.

"Built-in functions" <Built-in [email protected]>
wrote
in
message I have an Access Report where I would like only the last 4-digits of
the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Toniahb]

The origianl table lists all people primary name, spouse name, and dependent
name in one column. The SSN numbers are listed in another column. Then
there is a query for just the dependents and the primary person they are
linked to, and a query for just the spouse and the primary person they are
linked to. Another query was created with the format that I had mentioned
before combining the spouse query and dependent query. This query is then
put into a report. (PS I am not the orignal creator of this database, I was
just asked to put in a function to block the SSN numbers.)

Again:
"Can you share some table structures? Do you realize that a text box
using an expression like =Right([myfield],4) can't be named "myfield"?"

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)

--
Duane Hookom
MS Access MVP
--

I the header there are text controls that list the primary account holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can have
more than one child. Listed in the detailed section there is only 1 text
box
for dependents and their SSN, when the report is generated the SSN appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

Can you share some table structures? Do you realize that a text box using
an
expression like =Right([myfield],4) can't be named "myfield"?

--
Duane Hookom
MS Access MVP
--

My apologies to all, I wasnt clear enough. The dependents are listed
individually in te detailed section. When I apply the right function I
get
an error code. I am guessing the reason for this is because there are
multiple SSNs for multiple dependents. When there is just one SSN to
work
with like (myssn and spousessn) the right function works fine. I need
a
code
that will now that there are more than one dependent listed and can
block
the
first 5 digits of the ssn.

:

If you are asking the question, you are storing the dependent's
information
improperly. Each dependent should be stored in a single record so
there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would
need
to
separate the entries, apply the Right() function to each individual
SSN,
and
then concatenate them again for display. There are no shortcuts for
bad
table design.

If you are in a position to change the design, now would be a good
time.
You would add a small subform to the mainform to input dependent's
first
name, last name (remember they may be different), and SSN.

"Built-in functions" <Built-in [email protected]>
wrote
in
message I have an Access Report where I would like only the last 4-digits of
the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one. What
function do I use for that?

 

[Duane Hookom]

You seem to continue ignoring parts of my replies...
===============================
Do you realize that a text box using an expression like
=Right([myfield],4) can't be named "myfield"?

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)
===============================

--
Duane Hookom
MS Access MVP
--

The origianl table lists all people primary name, spouse name, and
dependent
name in one column. The SSN numbers are listed in another column. Then
there is a query for just the dependents and the primary person they are
linked to, and a query for just the spouse and the primary person they are
linked to. Another query was created with the format that I had mentioned
before combining the spouse query and dependent query. This query is then
put into a report. (PS I am not the orignal creator of this database, I
was
just asked to put in a function to block the SSN numbers.)

Again:
"Can you share some table structures? Do you realize that a text box
using an expression like =Right([myfield],4) can't be named "myfield"?"

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)

--
Duane Hookom
MS Access MVP
--

I the header there are text controls that list the primary account
holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can
have
more than one child. Listed in the detailed section there is only 1
text
box
for dependents and their SSN, when the report is generated the SSN
appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

:

Can you share some table structures? Do you realize that a text box
using
an
expression like =Right([myfield],4) can't be named "myfield"?

--
Duane Hookom
MS Access MVP
--

My apologies to all, I wasnt clear enough. The dependents are
listed
individually in te detailed section. When I apply the right
function I
get
an error code. I am guessing the reason for this is because there
are
multiple SSNs for multiple dependents. When there is just one SSN
to
work
with like (myssn and spousessn) the right function works fine. I
need
a
code
that will now that there are more than one dependent listed and can
block
the
first 5 digits of the ssn.

:

If you are asking the question, you are storing the dependent's
information
improperly. Each dependent should be stored in a single record so
there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would
need
to
separate the entries, apply the Right() function to each individual
SSN,
and
then concatenate them again for display. There are no shortcuts
for
bad
table design.

If you are in a position to change the design, now would be a good
time.
You would add a small subform to the mainform to input dependent's
first
name, last name (remember they may be different), and SSN.

"Built-in functions" <Built-in [email protected]>
wrote
in
message I have an Access Report where I would like only the last 4-digits
of
the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one.
What
function do I use for that?

 

[Toniahb]

Sorry, I AM NOT ignmoring your replies maybe I am not understanding your
question. I dont understand why "=Right([myfield],4) can't be named
"myfield"?"
it worked fine for other parts, just not when there is more than one ssn for
the same field i.e. the dependents. I tried your function and it did not
work, a parameter error appeared for the filed name.

You seem to continue ignoring parts of my replies...
===============================
Do you realize that a text box using an expression like
=Right([myfield],4) can't be named "myfield"?

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)
===============================

--
Duane Hookom
MS Access MVP
--

The origianl table lists all people primary name, spouse name, and
dependent
name in one column. The SSN numbers are listed in another column. Then
there is a query for just the dependents and the primary person they are
linked to, and a query for just the spouse and the primary person they are
linked to. Another query was created with the format that I had mentioned
before combining the spouse query and dependent query. This query is then
put into a report. (PS I am not the orignal creator of this database, I
was
just asked to put in a function to block the SSN numbers.)

Again:
"Can you share some table structures? Do you realize that a text box
using an expression like =Right([myfield],4) can't be named "myfield"?"

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)

--
Duane Hookom
MS Access MVP
--

I the header there are text controls that list the primary account
holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can
have
more than one child. Listed in the detailed section there is only 1
text
box
for dependents and their SSN, when the report is generated the SSN
appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

:

Can you share some table structures? Do you realize that a text box
using
an
expression like =Right([myfield],4) can't be named "myfield"?

--
Duane Hookom
MS Access MVP
--

My apologies to all, I wasnt clear enough. The dependents are
listed
individually in te detailed section. When I apply the right
function I
get
an error code. I am guessing the reason for this is because there
are
multiple SSNs for multiple dependents. When there is just one SSN
to
work
with like (myssn and spousessn) the right function works fine. I
need
a
code
that will now that there are more than one dependent listed and can
block
the
first 5 digits of the ssn.

:

If you are asking the question, you are storing the dependent's
information
improperly. Each dependent should be stored in a single record so
there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would
need
to
separate the entries, apply the Right() function to each individual
SSN,
and
then concatenate them again for display. There are no shortcuts
for
bad
table design.

If you are in a position to change the design, now would be a good
time.
You would add a small subform to the mainform to input dependent's
first
name, last name (remember they may be different), and SSN.

"Built-in functions" <Built-in [email protected]>
wrote
in
message I have an Access Report where I would like only the last 4-digits
of
the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one.
What
function do I use for that?

 

[Toniahb]

Duane, I final understand what you mean. The "name" of the field cannot be
the same as function name. Thank you. Everything works nows.

You seem to continue ignoring parts of my replies...
===============================
Do you realize that a text box using an expression like
=Right([myfield],4) can't be named "myfield"?

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)
===============================

--
Duane Hookom
MS Access MVP
--

The origianl table lists all people primary name, spouse name, and
dependent
name in one column. The SSN numbers are listed in another column. Then
there is a query for just the dependents and the primary person they are
linked to, and a query for just the spouse and the primary person they are
linked to. Another query was created with the format that I had mentioned
before combining the spouse query and dependent query. This query is then
put into a report. (PS I am not the orignal creator of this database, I
was
just asked to put in a function to block the SSN numbers.)

Again:
"Can you share some table structures? Do you realize that a text box
using an expression like =Right([myfield],4) can't be named "myfield"?"

Also, is it possible the DependentSSN field might be Null? If so, try:
=IIf(Len(DependentSSN & "") >4,Right(DependentSSN,4),Null)

--
Duane Hookom
MS Access MVP
--

I the header there are text controls that list the primary account
holder
and includes the SSN. For example

Name: [firstname]
SSN: [SSN]
Spouse:[spousename]
SSN: [SpouseSSN]

Then in the detailed section the dependents are linked to the primary
account holder through another table. The primary account holder can
have
more than one child. Listed in the detailed section there is only 1
text
box
for dependents and their SSN, when the report is generated the SSN
appears
for all the dependents. But I only want the last 4 to show.

=right([DependentSSN], 4) does not work , I get #Error.
But this function does work for those in the header.

:

Can you share some table structures? Do you realize that a text box
using
an
expression like =Right([myfield],4) can't be named "myfield"?

--
Duane Hookom
MS Access MVP
--

My apologies to all, I wasnt clear enough. The dependents are
listed
individually in te detailed section. When I apply the right
function I
get
an error code. I am guessing the reason for this is because there
are
multiple SSNs for multiple dependents. When there is just one SSN
to
work
with like (myssn and spousessn) the right function works fine. I
need
a
code
that will now that there are more than one dependent listed and can
block
the
first 5 digits of the ssn.

:

If you are asking the question, you are storing the dependent's
information
improperly. Each dependent should be stored in a single record so
there
would be no problem. You would use the same Right() function. If
dependent's data is mushed together into a single field, you would
need
to
separate the entries, apply the Right() function to each individual
SSN,
and
then concatenate them again for display. There are no shortcuts
for
bad
table design.

If you are in a position to change the design, now would be a good
time.
You would add a small subform to the mainform to input dependent's
first
name, last name (remember they may be different), and SSN.

"Built-in functions" <Built-in [email protected]>
wrote
in
message I have an Access Report where I would like only the last 4-digits
of
the
SSN
to show when printing. I have already used the function:
=right([myssn],4)
=right([spousessn],4)

But the problem is for the dependents, there is more than one.
What
function do I use for that?