Masking values in tables

[taa145]

I've a table with a field called 'Subscriber_ID" of 9 alph/numberic characters in MS Access. I would like to create an update query to update this field with the first 5 characters with ***** and last four characters as alpha/numeric.

So if run this update query it should update 123456789 to *****6789

Could someone tell me how could I do this? I would really appriciate it. Thanks!

[Killer42]

I've a table with a field called 'Subscriber_ID" of 9 alph/numberic characters in MS Access. I would like to create an update query to update this field with the first 5 characters with ***** and last four characters as alpha/numeric.
So if run this update query it should update 123456789 to *****6789
Could someone tell me how could I do this? I would really appriciate it. Thanks!

This is untested, but may be some help...

UPDATE Table1 SET Table1.Subscriber_ID = "*****" 
& Mid$("Subscriber_ID",6);
 

[NeoPa]

This should work perfectly.
Some (fussy) recommendations :
It's a good idea to use apostrophes (') for string delimiters within SQL where possible.
String versions of functions (Mid$, Format$) are no longer recommended. Use Mid instead.
Table qualifier unnecessary within a single table query (Field instead of Table.Field). Some people prefer to use it anyway - it's merely a style thing.

[taa145]

Thanks guys, I will try and hopefully it will work!

[Killer42]

This should work perfectly.
Some (fussy) recommendations :
It's a good idea to use apostrophes (') for string delimiters within SQL where possible.

Ok. Is that just to prevent conflicts when buildinmg strings in VB/VBA, or what?

String versions of functions (Mid$, Format$) are no longer recommended. Use Mid instead.

Yeah, I'm just a bit old-fashioned that way.

Table qualifier unnecessary within a single table query (Field instead of Table.Field). Some people prefer to use it anyway - it's merely a style thing.

I would normally leave it out, too. But I tend to use the Access GUI to generate SQL, and it likes to include them. At a guess, I probably stripped out a lot of parentheses before posting - Access just can't get enough of them, too.

[NeoPa]

Ok. Is that just to prevent conflicts when buildinmg strings in VB/VBA, or what?

No.
The real reason is that the ANSI standard for SQL string delimiters is actually the (') and not the (") and some SQL engines won't even recognise the ("). I have my SQL Server set to comply strictly with the ANSI standards so it doesn't. I find it's good practice.
But isn't the side-effect nice. Strings within strings are now very rarely the problem they used to be for me.

[taa145]

Man you guys are awsome. The solution worked the way i wanted with a little change. Here's how I am updading my records.

UPDATE IDCD_OUTBOUND_ORIG SET IDCD_OUTBOUND_ORIG.PRINT_MEMBER_ID = "*****" & Mid$([PRINT_MEMBER_ID],6)
WHERE (((IDCD_OUTBOUND_ORIG.GROUP_ID)="55657607"));

[PEB]

One suggestion:

Try not to use **** as symbols in your field! This will affect in problems doing Like criterias!

If there is a mask... The meaning of the mask is to cache some value behind...

Appears **** but behind to be 1234

So if you want this to be a real mask you need to touch to the mask properties of the respectif field in a table

[NeoPa]

I think there may be a misunderstanding here PEB - check out the first post.
The "****" is for obscuring secure information.

[PEB]

Yeah i understan that this is isn't a topic just for the masks
but instaed
Masking values in tables
Should be filling values in tables with ****

;)

[Killer42]

Yeah i understan that this is isn't a topic just for the masks
but instaed
Masking values in tables
Should be filling values in tables with ****

;)

I'd agree in general that masking should be done on display, rather than stored in the data. But if they really want to store it that way, it's their choice. However...

I would strongly recommend using some character other than the asterisk, if you can get away with it. Asterisks in your data are likely to cause all sorts of problems and confusion if you ever need to do wildcard type searches - that is, using the Like comparison. Because the asterisk has a special meaning in that case. As do some other characters, such as... um... question mark, I think. Check the online help for "Like", anyway.

Also, you might want to keep in mind NeoPa's recommendation to use single rather than double quotes. In other words, rather than

UPDATE IDCD_OUTBOUND_ORIG SET IDCD_OUTBOUND_ORIG.PRINT_MEMBER_ID = 
 "*****" & Mid$([PRINT_MEMBER_ID],6)
WHERE (((IDCD_OUTBOUND_ORIG.GROUP_ID)="55657607"));

you could use

UPDATE IDCD_OUTBOUND_ORIG SET IDCD_OUTBOUND_ORIG.PRINT_MEMBER_ID =
 '*****' & Mid$([PRINT_MEMBER_ID],6)
WHERE (((IDCD_OUTBOUND_ORIG.GROUP_ID)='55657607'));

(Urg - the highlighting didn't work very well, did it?)

[Killer42]

Sorry, I overlooked the fact that PEB already pointed out the problem with Like.

[taa145]

Thanks guys for all your suggestions!