Results 1 to 6 of 6
  1. #1
    wellsw is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2013
    Posts
    7

    Replace _ when #_#


    I have a table with entries like this:
    Apple_orange_cherry_1_578

    The query result I need is
    Apple_orange_cherry_1.578

    I have the following query:
    Expr1: Replace([Field1],"1_5","1.5")

    which works for this instance but what I really need it it to work for any numbers.

    I have been looking all over for an example query and cant find one.

    Thanks!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It looks like you need to replace the *last* underscore in the string which you can find with the InStrRev() function.

  3. #3
    wellsw is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2013
    Posts
    7
    I guess I better define my data
    the table contains

    Apple_orange_cherry_1_578_grape
    Banana_orange_blueberry_2_mango
    Apple_orange_cherry_6_478_grape

    the query result needs to be

    Apple_orange_cherry_1.578_grape
    Banana_orange_blueberry_2_mango
    Apple_orange_cherry_6.478_grape
    it seems there should be something like
    Replace ([Field1],"_",".") Where #"_"#

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    I suggest you clarify what the rules are.

    Is it
    If there are 5 "_" in the string, then change the 4th to "."

    What does the data represent? Any possibility to add the"." appropriately on input?
    It looks like poor field/table design, but we have no idea of what your "business" is.

  5. #5
    wellsw is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2013
    Posts
    7
    the rules are:

    if there is a "_" preceeded and followed by a number replace the "_" with a "."
    I cant use a count because there are too many variables.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's a Function that you could put in a Standard Module:
    Code:
    Public Function ParseIt(strIn As String) As String'...If there is a "_" preceeded and followed by a number replace the "_" with a "."
    '...so
    '...Apple_orange_cherry_1_578_grape
    '...Banana_orange_blueberry_2_mango
    '...Apple_orange_cherry_6_478_grape
    '
    '...becomes...
    '...Apple_orange_cherry_1.578_grape
    '...Banana_orange_blueberry_2_mango
    '...Apple_orange_cherry_6.478_grape
       Dim MyArray() As String
       Dim MyDelim As String
       MyDelim = "_"
       Dim x As Long
       '-- Split out the incoming string
       MyArray = Split(strIn, "_")
       '--Now build the output string
       For x = 0 To UBound(MyArray) - 1
          If IsNumeric(MyArray(x)) Then
             If IsNumeric(MyArray(x + 1)) Then
                MyDelim = "."
             Else
                MyDelim = "_"
             End If
          End If
          ParseIt = ParseIt & MyArray(x) & MyDelim
       Next
       '--add the last element w/o the trailing "_"
       ParseIt = ParseIt & MyArray(UBound(MyArray))
    End Function
    You would use it like this: Afield: = ParseIt([YourField]) in a query.

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

Similar Threads

  1. replace . with /
    By msasan1367 in forum Access
    Replies: 1
    Last Post: 08-05-2013, 06:21 AM
  2. Help with Replace Function...
    By redbull in forum Programming
    Replies: 5
    Last Post: 06-27-2013, 04:05 PM
  3. Replies: 3
    Last Post: 06-07-2012, 07:05 AM
  4. find and replace
    By rohini in forum Access
    Replies: 7
    Last Post: 05-17-2012, 05:23 AM
  5. auto replace??
    By d3pl0y3d in forum Access
    Replies: 2
    Last Post: 02-11-2011, 02:09 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
  •  
Other Forums: Microsoft Office Forums