Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Is There Any Way to Format This Data?

    Hey guys,

    I have an Access table with a field for social security number. It displays as:

    333-33-3333

    I want to change the formatting so it will only display the last 4 digits. I can't not figure a way to do this. I've played with the FORMAT properties, but I can't find a tutorial or example on truncating data in a field or partially displaying part of a data.

    Is there a way to do this? I am on Access 2003.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can use the Right() function to only display the last 4 digits.
    Paul

  3. #3
    Join Date
    Jan 2009
    Posts
    46
    I've been trying that, but I'm doing something wrong. My field name is SSN so I've tried RIGHT(SSN,4). Nothing happens. =RIGHT(SSN, 4), and "RIGHT(SSN,4)".

    What is the right syntax here??

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're not directly in the table, are you? Most of us never let users see the tables or queries, just forms and reports. The Right() function will work in queries, forms, reports and code, but probably not directly in a table.
    Paul

  5. #5
    Join Date
    Jan 2009
    Posts
    46
    I've tried it on the Report Design as well, and same results. If I enter:
    RIGHT(SSN,4)

    Access automatically changes it to
    "RIG"h"T("ssn",4)".


    Argh, it shouldn't be this difficult. This is a text field btw.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In the control source of a textbox, try

    =RIGHT(SSN,4)

    That presumes that SSN is a field in the underlying data.
    Paul

  7. #7
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by pbaldy
    In the control source of a textbox, try

    =RIGHT(SSN,4)

    That presumes that SSN is a field in the underlying data.
    I get a syntax error when using this code in the control source. I wish I could just find an example of the RIGHT function, but been searching for hours online, and nothing.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db, because this should be really easy.
    Paul

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Careful, you might get a db full of SSN's that you probably don't want to be liable for...

    What are you doing to secure this database? I feel obligated to ask, sorry.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Good point. I assume people wouldn't post sensitive info, but I guess it can be easy to forget. We definitely don't want real SSN's posted.
    Paul

  11. #11
    Join Date
    Jan 2009
    Posts
    46
    I might see if I can send the file with dummy data. For now, here is an image showing you where I am trying to put the function and the error I get.

    http://i880.photobucket.com/albums/a...94-22-47PM.png

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    =RIGHT(SSN,4)

    This syntax is correct.


    Access automatically changes it to
    "RIG"h"T("ssn",4)".
    This tells me you are putting it into the Format or Input Mask properties instead of the Control Source.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by StarTrekker
    =RIGHT(SSN,4)

    This syntax is correct.



    This tells me you are putting it into the Format or Input Mask properties instead of the Control Source.
    Yes, you are right in that I originally tried to put this into Format, but it fails in Control Source as well. Or would it be =RIGHT([SSN], 4)??

    I'm sure it is something minor that I'm missing here.

    I created a simple mdb with dummy data. I can't get the RIGHT function to work on my report here either. Can anyone tell me why this fails or causes an error? Thanks!
    Attached Files Attached Files

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're running into an Access quirk where it doesn't like the textbox to have the same name as the field in the data. Rename the textbox to txtSSN and try it (watch out for another quirk, where Access may "helpfully" change your Right function to use the new textbox name).
    Paul

  15. #15
    Join Date
    Jan 2009
    Posts
    46
    Thanks Paul. I tried your tip, and I was finally able to get the RIGHT function to work with my test file. Unfortunately, renaming the text box and entering he exact same function into my live database still generates a compile error.

    I even went as far as to create the same dummy table and run the report off that, but it still failed. I can't think of anything else that would be different between the two files.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •