Solved

Access 2003 report - masking a credit card number

Posted on 2009-04-10
18
387 Views
Last Modified: 2012-05-06
I have an Access 2003 form, and a report. On the form I capture a credit card #, but when I print the report, to be in compliance with the law I have to fill in all XXXX for the credit card number, ONLY showing the last 4 digits. I do not know how to do this on the report..
thx experts...Bob
0
Comment
Question by:bobrossi56
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 75
ID: 24120601
Example:

String(Len("1234-1234-1232-1234")-4,"X") & Right("1234-1234-1232-1234",4)

returns  XXXXXXXXXXXXXXX1234

So, in a text box on your report, put this as the Control Source:

=String(Len([YourCreditCardField])-4,"X") & Right([YourCreditCardField],4)

mx
0
 
LVL 75
ID: 24120607
Bob ... as a side note, there is no way I would probably store credit card numbers in an Access database - as the security just isn't there.  So, unless you can secure this database in some clever way - I would give that some thought.

mx
0
 

Author Comment

by:bobrossi56
ID: 24120620
I put this in the control source of the report for the field CreditNumer and it returns #Error. The field on the form is called creditnumer, the database field is called creditnumer, and the report field is called creditnumer.

=String(Len([CreditNumer])-4,"X") & Right([CreditNumer],4)
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 75
ID: 24120686
Try this:

= IIf(Not IsNull([CreditNumer]), String(Len([CreditNumer]) - 4, "X") & Right([CreditNumer], 4), Null)

mx
0
 
LVL 75
ID: 24120698
seems to work for any combination of numbers - ie any CC format.  Needed to take into account Null records.

mx
0
 

Author Comment

by:bobrossi56
ID: 24121847
Still getting the #error mx. Does it matter that type of field it is, text vs. number?
0
 
LVL 75
ID: 24122860
"Does it matter that type of field it is, text vs. number?"
No ... what I originally posted I tested.  Strange ...

Can you:

1) Compact & Repair (to shrink the size),
2) Zip up the MDB (to further shrink the size)
3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.
4** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

mx
0
 

Author Comment

by:bobrossi56
ID: 24122972
OK, no data in here I care about.
Again, when I enter a credit card number on the form, and generate the report via the PDF button, I get #error on the report, instead of XXXXXXXXXXXX6471. Only 1 report and form is Contracts

I have another question if I could while you have the database in your hands, should I submit another question in hopes you get it?
Limo-Rental.mdb
Limo-Rental-be.mdb
0
 
LVL 75
ID: 24123009
ok standby ....
0
 

Author Comment

by:bobrossi56
ID: 24123014
Actually I just figured out my other problem, I now just have the credit card XXXX issue.
0
 
LVL 75
ID: 24123041
I found the problem - will upload standby.

mx
0
 
LVL 75
ID: 24123051
see attached mdb ...

mx
Limo-Rental-MX01.zip
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 24123084
Reason:
On the report ... the Name of the Control for this expression was the same as the Field Name (CreditNumer) ... which Access does by default.  Unfortunately in Reports especially, Access gets confused when the control name is the same as something in - this case - and expression.  I renamed the control to txtCreditNumer ... and now it works.  Keep this in mind for the future ... always give the control a different name than the Field name if you are going to use that field in a calculation on the report or refer to it in code.

A bigger issue:  Because in Tools>>Options>>General tab ... Track Name AutoCorrect Info was on (by Default) ... when I first renamed the control to txtCreditNumer ... Access rename CreditNumer in the expression to txtCreditNumer ... and it STILL didn't work!  What!!!  Of course, I immediately noticed that ... and knew what the problem was.

The moral to this:  *Always* ... in any new mdb you create or inherit ... immediately turn of Track name AutoCorrect info.  Unfortunately ... this feature was not implemented correctly and has been known case a LOAD of bugs ... as well as creating the little issue I had here.  Always turn this off.

Here is a reference on this:

http://allenbrowne.com/bug-09.html

Also ... read the remainder of that article on the other properties that cause issues.

mx
0
 

Author Comment

by:bobrossi56
ID: 24123108
mx, I hate to be a kill-joy, but it still give me #error. here is what I did.
1. Turned off that rack Name AutoCorrect  in my database
2. Opened my report and chanhed the credit card field name to txtCreditNumer
3. Then I took the code from your database =IIf(Not IsNull([CreditNumer]),String(Len([CreditNumer])-4,"X") & Right([CreditNumer],4),Null) and put it into my report's credit card field.
4. Then I created a new record, made a PDF, opened said PDF and in the credit card field I get #errror
0
 

Author Comment

by:bobrossi56
ID: 24123120
However, if I replace my front end DB with yours, and relink the tables, it works fine. ARGHHH!!!
0
 

Author Comment

by:bobrossi56
ID: 24123145
I copied and pasted your report field onto my report and it works fine now. GO figure.
thanks for ALL your help mx.
0
 

Author Closing Comment

by:bobrossi56
ID: 31569125
mx is the best, great help.
most asppreciative
0
 
LVL 75
ID: 24123188
"Grade:  A - "mx is the best, great help.
most asppreciative"  "

Always a pleasure Bob ... thx.

mx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question