Replace _ when #_#

[wellsw]

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!

[RuralGuy]

It looks like you need to replace the *last* underscore in the string which you can find with the InStrRev() function.

[wellsw]

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 #"_"#

[orange]

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.

[wellsw]

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.

[RuralGuy]

Here's a Function that you could put in a Standard Module:

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.