Results 1 to 3 of 3
  1. #1
    sgtclark is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2020
    Posts
    24

    Creating an Update query using the Replace() Function

    I am trying to use the Replace() Function in an update queryto select a specific text string followed by a date. I want the date(s) to bewild or where it will select any date following the specific text. It must keepthe text before the text string and after the date.


    Example: in field [StatusComment] = Order Approved File at DCS as of 2/21/2020 Pending Action



    I can get Replace([Status Comment], ‘File at DCS as of 2/21/2020’, ‘ ‘) to work just fine resulting in
    [StatusComment] = Order Approved Pending Action

    What I need is for the date to not be specific i.e.##/##/#### or something of the sort.
    Does anyone of any idea of how to select thedesired text string to include whatever date follows in this scenario of theReplace()?
    Last edited by sgtclark; 03-02-2020 at 10:53 PM. Reason: Spelling

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't use Replace with wildcard. Have to break up string, then recombine.

    Or maybe use Regex in a VBA custom function. https://www.wallstreetmojo.com/vba-regex/
    Last edited by June7; 03-03-2020 at 01:48 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show us a few more examples to get a better idea? Is the string being replaced always starting with/containing" File at DCS as of"? If you can format your dates to be MM/DD/YYYY then you could use the Mid function along with Instr:

    Replace([Called Name]," File at DCS as of " & Mid([Called Name],InStr([Called Name],"/")-2,10),"")

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replace Function In A Query
    By jo15765 in forum Queries
    Replies: 6
    Last Post: 06-01-2017, 10:54 AM
  2. Update query with replace function fails
    By Keven in forum Queries
    Replies: 5
    Last Post: 07-11-2016, 08:59 AM
  3. Replies: 3
    Last Post: 04-02-2014, 10:31 AM
  4. Update with replace query help
    By goomba79 in forum Queries
    Replies: 4
    Last Post: 12-14-2012, 08:27 AM
  5. Replies: 3
    Last Post: 06-07-2012, 07:05 AM

Tags for this Thread

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