Results 1 to 5 of 5
  1. #1
    MJays is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    3

    Goal: To remove "S - Bayside Peninsula - " from every record. But not all records hve the last dash


    Hi All,

    I'm a newby, so not sure if I'm doing this right, but I have a problem I can't work out. (Too old and gnarly.)

    An excel spreadsheet is provided to us from an external source. We can't change the way they collect it. But we need to run it through Access to create several detailed reports.

    In this case, I need to remove "S - Bayside Peninsula - " from all records in a query, but keep the data that follows the last dash (and space). However, some of the records do not contain the last dash, and have just the space before the data I need to keep.

    I have been trying to work out how to do an IIf to add a dash to those records that don't have one, then use a String Function to remove what I don't want.

    I'm not an Access wizard.... just trying to help my colleagues who are even worse than I am!!

    Example:

    S - Bayside Peninsula - Contracted Case Management 1
    S - Bayside Peninsula Investigation Team 7

    Any assistance would be greatly appreciated.

    MJ (Mary-Jane)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,990
    In the import query,(or update qry)
    set 1 field to filter only the S recs, then change it:

    Criteria:
    left([field],3) ="S -"

    Update to:
    Mid([field],4)

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,985
    how about

    trim(replace(replace(myfield,"S - Bayside Peninsula",""),"-",""))

    or if all records start with '
    S - Bayside Peninsula'

    trim(replace(mid(myfield,22),"-","")

  4. #4
    Join Date
    Apr 2017
    Posts
    1,071
    Code:
    replace(replace(myfield,"S - Bayside Peninsula -",""),"S - Bayside Peninsula",""))
    The idea is, that 1st replace searches for longer string, and when it finds it, then replaces it with empty string, otherwise doesn't change it. After that, second replace searches for shorter string (when 1st search did replacing, then obviously it is not found now), and when it finds it, then replaces it with empty string, otherwise doesn't change it.

    (Ajax solution works only, when dash is not used anywhere else in same field in any row. Probably it will work too, but it is not foolproof.)

  5. #5
    MJays is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    3
    Thank you, Ajax.

    Both options worked perfectly.

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

Similar Threads

  1. Replies: 5
    Last Post: 06-26-2019, 12:18 PM
  2. Replies: 2
    Last Post: 01-23-2017, 07:06 PM
  3. Replies: 2
    Last Post: 02-27-2015, 10:03 PM
  4. Replies: 4
    Last Post: 07-12-2014, 02:02 PM
  5. Replies: 8
    Last Post: 04-18-2013, 04:41 PM

Posting Permissions

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