Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    I mean the literal text (at least if I understand the context of your use of that). In other words, if the data is records in a table you really can't write an expression (or should not try to) for every possible value. To me, the use of Replace function would only be practical for maybe up to ten different corrections AND only if that will be a one time thing. I have no idea what an EDI file is but can only presume it's a large chunk of data, which means that would probably make individual corrections impractical. Plus, OP said that a missing field is replaced with * not ** so I think yours wouldn't work for an odd number of * because those expressions are looking to replace pairs. Regardless, as usual there is more not being said about the situation than said, which is typical and perhaps lamentable.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Micron View Post
    In other words, if the data is records in a table you really can't write an expression (or should not try to) for every possible value. To me, the use of Replace function would only be practical for maybe up to ten different corrections AND only if that will be a one time thing.
    I am sorry but, I am still don't understand.
    The inner Replace, removes pairs of asterisks and the outer Replace removes the last asterisk in case of odd number of asterisks.

    In a query expression, the name of the relevant field could take the place of literal text value -- unless you talk about performance.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The inner Replace, removes pairs of asterisks and the outer Replace removes the last asterisk in case of odd number of asterisks.
    Sorry, I missed that.
    the name of the relevant field could take the place of literal text value
    If you said that, I missed it. If not, then to be fair, your post suggested otherwise and for that reason I didn't look closely enough. I do like the idea of the nested replace if referring to a field though. Thanks for sticking it out!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Micron View Post
    If you said that, I missed it.
    No I didn't, I didn't consider it necessary because I suggested it as a general option to a non detailed question.
    Quote Originally Posted by Micron View Post
    Thanks for sticking it out!
    You are welcome!

  5. #20
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Code:
    x = "SV1*HC:90686*33*UN*1****************~"
    
    Do
        If Right$(x, 2) = "*~" Then
            x = Left$(x, Len(x) - 2) & "~"
        Else
            Exit Do
        End If
    Loop
    or a more compact version
    Code:
    Do
        If Right$(x, 2) = "*~" Then x = Left$(x, Len(x) - 2) & Right$(x, 1) Else Exit Do
    Loop

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 04-08-2020, 09:18 PM
  2. Extract characters from the String
    By soldat452002 in forum Access
    Replies: 12
    Last Post: 10-02-2018, 09:31 PM
  3. Get trailing Characters from String
    By jaykappy in forum Queries
    Replies: 4
    Last Post: 09-03-2014, 09:19 AM
  4. Replies: 1
    Last Post: 05-02-2013, 10:57 AM
  5. function to strip characters
    By Kirsti in forum Queries
    Replies: 2
    Last Post: 11-01-2012, 07:53 PM

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