Find and replace

[Lloyd247]

I have been able to use find/replace on a field in a table but unable to execute it in a query, any ideas or suggestions?

Example in table find/replace that works
Field contents: <CatchWeight><CatchWeight>37.78</CatchWeight><CatchWeight>33.74</CatchWeight><CatchWeight RejectedIndicator="true">33.36
Find What: <CatchWeight>##.##</CatchWeight>
Replace with 1 Space
Results: space space <CatchWeight RejectedIndicator="true">33.36

Query field: T1: Replace([catchweights],"<CatchWeight>##.##</CatchWeight>"," ")

Query fails to convert

 

[CJ_London]

The replace function uses literal strings so is looking for ##.## and does not substitute numbers.

You will need to use the instr function to determine the first and last characters of the string you want to replace with a space

given you gave two strings to replace and with a limited example which does not actually work (your replace has an extra >), I’ll leave it to you to determine the best way

for example this would produce the required result but without knowing the range of strings and outcomes required, no idea if it is of use

“  “  & Mid([catch-weights],instrrev([catch-weights],”reject”)

note on my phone which uses smart quotes

 

[Lloyd247]

The replace function uses literal strings so is looking for ##.## and does not substitute numbers.

You will need to use the instr function to determine the first and last characters of the string you want to replace with a space

given you gave two strings to replace and with a limited example which does not actually work (your replace has an extra >), I’ll leave it to you to determine the best way

for example this would produce the required result but without knowing the range of strings and outcomes required, no idea if it is of use

“  “  & Mid([catch-weights],instrrev([catch-weights],”reject”)

note on my phone which uses smart quotes

Thank you for the quick response. The example I provided is formatted as long text and they store xml code in that field, both delivered and rejected weights are in the field. Any thoughts on how to separate them? see below for line breakdown:

Note 1 - Normal Delivered qty:
<CatchWeights><CatchWeight>37</CatchWeight><CatchWeight>35.06</CatchWeight><CatchWeight>39.44</CatchWeight></CatchWeights>
This stores 3 weights delivered -- Invoice line will display |37.00| 35.06|39.44|

Note 2 - Rejected partial qty:
<CatchWeights><CatchWeight RejectedIndicator="true">33.54</CatchWeight><CatchWeight RejectedIndicator="true">33.74</CatchWeight><CatchWeight>37.78</CatchWeight></CatchWeights>
This stores 2 weights rejected and 1 weight delivered -- Invoice line will display |33.545|33.74|37.78| and the returned invoice line will display |33.54|33.74|

Lloyd

 

[ebs17]

Isolating the values is a task for regular expressions.

 

[Mike Krailo]

I did this a different way. Using a function to iterate through each individual character of the string, the numeric values are retained and all other values are turned into spaces. Then using another function to squeeze all the spaces down to one space the data is neatly separated. You can replace the spaces with whatever character you want, I used the vertical pipe as in your example. See attached example database. Run the query and see the last two columns in the query.

Function GetAllNumeric(ByVal pStr As String) As String
    Dim NewValue As String
    Dim Length As Long
    Dim n As Integer
    
    Length = Len(pStr)
    
    ' Iterate through each character of the string and replace non-numeric characters with spaces
    For n = 1 To Length
        If (IsNumeric(Mid(pStr, n, 1))) Or (Mid(pStr, n, 1) = ".") Then
            NewValue = NewValue & Mid(pStr, n, 1)
        Else
            NewValue = NewValue & " "
        End If
    Next
    
    GetAllNumeric = SuperTrim(NewValue)
    ' Separate values by vertical pipe
    GetAllNumeric = Replace(GetAllNumeric, " ", "|")
    
End Function

' Like normal trim function but also reduces multiple spaces inside the string to one single space
Function SuperTrim(ByVal pStr As String) As String
    Dim NewString As String
    NewString = pStr
    Do
       NewString = Replace(NewString, "  ", " ")
    Loop Until InStr(1, NewString, "  ") = 0
    SuperTrim = Trim(NewString)
End Function

 

[CJ_London]

From my understanding of the requirement:

This is my code

Function getNums(s As String, nType As Boolean) As String
'nType - true=invoice, false=reject
Dim a() As String
Dim i As Integer
Dim b As String

    a = Split(s, "</CatchWeight>")
    For i = 0 To UBound(a)
        If nType Or (Not nType And InStr(a(i), "RejectedIndicator") > 0) Then   'include
            b = b & "|" & Format(Mid(a(i), InStrRev(a(i), ">") + 1), "#,###.00")
        End If
    Next i
    If b <> "" Then getNums = Replace(b & "|", "||", "|")
   
End Function

Pass true to get the invoice list, false to get the rejects - see the qryGetInvoiceAndRejects query

 

[Lloyd247]

From my understanding of the requirement:

This is my code

Function getNums(s As String, nType As Boolean) As String
'nType - true=invoice, false=reject
Dim a() As String
Dim i As Integer
Dim b As String

    a = Split(s, "</CatchWeight>")
    For i = 0 To UBound(a)
        If nType Or (Not nType And InStr(a(i), "RejectedIndicator") > 0) Then   'include
            b = b & "|" & Format(Mid(a(i), InStrRev(a(i), ">") + 1), "#,###.00")
        End If
    Next i
    If b <> "" Then getNums = Replace(b & "|", "||", "|")
  
End Function

Pass true to get the invoice list, false to get the rejects - see the qryGetInvoiceAndRejects query

Thank you, the samples you provided did exactly what I needed. Lloyd