How do I mask or replace sensitive data returns?

[ljcuellar]

I have a query that can return sensitive data in specific columns. I am looking to mask or replace the data (with Confidential) in one of the other columns. If the sensitive data indicator is returned in one column, I would like to mask the data or replace the data (Confidential) in a different column. All other data in other columns can be displayed. First time posting, hopefully this makes sense. Thank you.

[Micron]

I suppose a calculated query field would work. Give it an alias name and use IIF function to return either the data or "Confidential". Assuming that the indicator is True or False, perhaps

ALIAS: IFF([indicator]=False,[dataField],"Confidential")

If users are directly interacting with queries that's the first thing you should fix. Should only use forms/reports.

[Welshgasman]

To mask, you could use the Password format?

tt=12345
? tt
 12345 
? format(tt,"Password")
Pa006or20

[orange]

Who has to see these confidential values? There may be an/some options to restrict access to these.
A separate table with 1:1 relationship and user access control to specific accounts.
We really need to understand the requirements in order to offer more focused responses.

You may be able to devise an Encrypt and Decrypt set up, but details of your needs are needed.

[Micron]

WGM, you would want to be very careful that you didn't use that in a way that will alter the data even if you could get it to work in a query.
If you can, please post an example of the syntax as I couldn't do it.

[Welshgasman]

I just tried it out in the immediate window, to see if it would work?
Code is in post 3

[Micron]

I just tried it out in the immediate window, to see if it would work?
Code is in post 3

I can see that. I'm asking if you can do it in a query because the OP is using one. I tried but cannot.

[Welshgasman]

Just a quick query

SELECT TestTransactions.Amount, Format([Amount],"""Pa""ssw""or""d") AS Expr1
FROM TestTransactions;

Something strange going on here. I keyed in Format([Amount],"Password"), yet Access changed it to above. I removed the second column and tried again, same thing?

In fact just using "*********" will mask the contents?

[Welshgasman]

This is interesting

SELECT TestTransactions.Amount, Format([Amount],"""*******""") AS Expr1, Format([Amount],"c\on""fi""d\en""tial""") AS Expr2
FROM TestTransactions;

I just entered "Confidential" as the format?

[Welshgasman]

I am wondering, why not just omit the confidential data?

[isladogs]

End users should NEVER look at tables or query results.
All interaction should be done via forms or reports.

For fields with confidential data on reports, you can easily make that data 'invisible' using conditional formatting where the text colour (fore colour) is set equal to the back colour.
OR you can mask it using the IIf function as Micron mentioned in post #2 (NOTE: IIf NOT Iff)

@Welshgasman
As I think you've already realised, you can't use formatting for this purpose

[Micron]

The difference seems to be that when I saw what Access did to the format parameter (as you posted) I didn't even try to run it, figuring it wouldn't work. Thread seems to be morphing into formatting quirks. As for me, I think I will refrain from commenting until OP responds to Orange.

EDIT - Yes, IIF. That was before and after a rough night.