Masking Characters

[Neil]

In an Access form, I need to hide part of a SSN in certain conditions, and
show the whole thing in others. In other words, sometimes:

123-45-6789

and other times:

***-**-6789

Can that be done with the Format property?

 

[John W. Vinson]

In an Access form, I need to hide part of a SSN in certain conditions, and
show the whole thing in others. In other words, sometimes:

123-45-6789

and other times:

***-**-6789

Can that be done with the Format property?

The Format property isn't that flexible. One way to do it would be to base the
form or report on a Query, and in the query put a calculated field

ShowSSN: "****-**-" & Right([SSN], 4)

Of course if the user has a way to look at the table or queries directly they
could find the actual SSN easily, so this isn't any sort of real security; but
it will at least conceal the data from casual viewing.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Neil]

In an Access form, I need to hide part of a SSN in certain conditions, and
show the whole thing in others. In other words, sometimes:

123-45-6789

and other times:

***-**-6789

Can that be done with the Format property?

The Format property isn't that flexible. One way to do it would be to base
the
form or report on a Query, and in the query put a calculated field

ShowSSN: "****-**-" & Right([SSN], 4)

Of course if the user has a way to look at the table or queries directly
they
could find the actual SSN easily, so this isn't any sort of real security;
but
it will at least conceal the data from casual viewing.

Thanks, John. Security isn't a major concern. The only thing is, in deciding
which field to use (SSN or ShowSSN, as you've called it), I'd need to us an
IIF statement in an unbound text box. That creates problems with continuous
form view. Oh well. Guess you can't have your cake and eat it too....

 

[John W. Vinson]

Thanks, John. Security isn't a major concern. The only thing is, in deciding
which field to use (SSN or ShowSSN, as you've called it), I'd need to us an
IIF statement in an unbound text box. That creates problems with continuous
form view. Oh well. Guess you can't have your cake and eat it too....

Not sure at what point you decide which is which, but I'd be inclined to have
two forms - one with the full SSN for data entry and editing, and a different
form for users who shouldn't see the full number. You could even have the two
forms in two different frontends, if the deciding factor is "whose PC should
this be on".
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Neil]

Not sure at what point you decide which is which, but I'd be inclined to
have
two forms - one with the full SSN for data entry and editing, and a
different
form for users who shouldn't see the full number. You could even have the
two
forms in two different frontends, if the deciding factor is "whose PC
should
this be on".

Right, that would make sense. But in this case it's not based on the user,
it's based on the record. If the record has a certain status, the user
should see the SSN; but with a different status, only partial SSN. (Don't
ask me why. I don't know. )

So the same user would be seeing SSNs in some records and partial SSNs in
others.

But I'm not sure if they're going to be using Continuous Forms view anyway.
But it would be nice for them to have that option. But if it can't be, then
it can't be.

Thx.

 

[John Spencer]

Then you could generate the proper view depending on the status.

Field: ShowSSN: IIF(Status = "A",[SSN],"***-**-} & Right([SSN],4))

Of course, this means that the SSN cannot be edited or changed or added
without some more manipulation of controls on the form.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Access Developer]

Instead of trying to manipulate the view of the SSN in the form, manipulate
it in a Query and display the contents of the Query in the Form. Even if
you use the SSN as the record key (which is Not A Good Idea, BTW, for
multiple reasons), you could not display the actual SSN, only the formatted
SSN which you create in a Calculated Field. This will eliminate the problem
with continuous forms.