Results 1 to 4 of 4
  1. #1
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80

    Search Form and SSN


    I have a search form that searches the database. There is just 1 text box and it searches for SSN, Last Name, First Name, Record ID, etc. The trouble I am having is the system saves SSN's like xxxxxxxxx, vs xxx-xx-xxxx. Unfortunately different people enter them differently by habit so when someone uses the search field with xxx-xx-xxxx it does not pull them up because the -'s are not stored. Is there a way to have it exclude these when doing the SSN search so it will accept both ways of entering? Below is the code for the After Update event:

    Code:
    [SSN] Like "*" & [Forms]![frmRecordsSearch]![SearchFieldTxt] & "*" Or [LastName] Like "*" & [Forms]![frmRecordsSearch]![SearchFieldTxt] & "*" Or [FirstName] Like "*" & [Forms]![frmRecordsSearch]![SearchFieldTxt] & "*"

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Replace([Forms]![frmRecordsSearch]![SearchFieldTxt], "-", "")

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    something like

    [SSN] Like "*" & replace([Forms]![frmRecordsSearch]![SearchFieldTxt],"-","") & "*"

  4. #4
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    Worked great, thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 09-08-2016, 08:09 PM
  2. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  3. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  4. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  5. Replies: 1
    Last Post: 04-20-2012, 03:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums