none
Show only last four digits of account number

    Question

  • I have a letter that is generated from my access db which shows the individual's account number.  In the past, we have shown the entire account number, but now we only want to show the last four digits of the account number.  The field in the underlying table is called "Account" and it is a text field.  How would I format the report letter so that only the last four digits showed up on the printed report?  Thanks for your help.
    Friday, October 18, 2013 3:45 PM

Answers

  • Easy-squeezy!

    ControlSource: =Right([Account], 4)

    There are other such functions that you might find useful in the future. Check Help for String Functions.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by jdmcleod Friday, October 18, 2013 5:40 PM
    Friday, October 18, 2013 4:02 PM
  • If you want to substitute asterisks for the leading numbers, as is commonly done for security purposes, you can extend the expression like this:

    =String(Len([Account])-4,"*") & Right([Account],4)

    Ken Sheridan, Stafford, England

    • Marked as answer by jdmcleod Friday, October 18, 2013 5:58 PM
    Friday, October 18, 2013 5:55 PM

All replies

  • Easy-squeezy!

    ControlSource: =Right([Account], 4)

    There are other such functions that you might find useful in the future. Check Help for String Functions.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by jdmcleod Friday, October 18, 2013 5:40 PM
    Friday, October 18, 2013 4:02 PM
  • That worked.  At first I had an error message but that was because my field name and the text box name were the same.  once I changed the text box name, it worked perfectly.  Thanks for your help.
    Friday, October 18, 2013 5:40 PM
  • If you want to substitute asterisks for the leading numbers, as is commonly done for security purposes, you can extend the expression like this:

    =String(Len([Account])-4,"*") & Right([Account],4)

    Ken Sheridan, Stafford, England

    • Marked as answer by jdmcleod Friday, October 18, 2013 5:58 PM
    Friday, October 18, 2013 5:55 PM
  • Thanks Ken, that's a great idea.  Is the Len the same in Access as it is in Excel, the length.  By doing this, it would show asterisk for the correct number of digits.  If one account was 8 characters it would have 4*, whereas another account number that was 9 in length would have 5* asterisk?
    Friday, October 18, 2013 5:58 PM
  •  Is the Len the same in Access as it is in Excel, the length.  By doing this, it would show asterisk for the correct number of digits.  If one account was 8 characters it would have 4*, whereas another account number that was 9 in length would have 5* asterisk?
    Yep, that's exactly how it works.

    Ken Sheridan, Stafford, England

    Friday, October 18, 2013 6:03 PM
  • My pleasure.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, October 18, 2013 8:51 PM
  • When I add this to my select statement where do I place this at?
    Monday, March 31, 2014 6:01 PM
  • When I add this to my select statement where do I place this at?

    It would go on the field line in the design view grid This is what is called an alias.

    Last4: String(Len([Account])-4,"*") & Right([Account],4)

    The SELECT statement would look like this in SQL view:

    SELECT String(Len([Account])-4,"*") & Right([Account],4) as Last4
    FROM MyTable

    -or-

    Use it as a ControlSource. Putting it in the query is probably more secure, but Access is not meant for high security.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Monday, March 31, 2014 6:30 PM