Strip Out Text from Memo Field

I have a Memo field in table that I would like strip out the text between two markers and then write the results to a seperate text field. The first marker is UFMS: and the seceond maker is either single quote or a double quote. In the example below the data that I would like to capture is ---  Obligation Amendment is not processed as Traveller or Travel Site is not active in UFMS Fi   02          0O1PN

I have also attached the db with the table -- Thanks



Example:

Transaction ID: 0O1PNL004               Reference #: 0O1PNL004
` ERROR CODE                                           ERROR DESCRIPTION                                LINE ID         TRN
`             Under review by your agency financial system.
`
`  UFMS:
`             Obligation Amendment is not processed as Traveller or Travel Site is not active in UFMS Fi   02          0O1PNL
`             nancia ...
`             Obligation Amendment is not processed as Traveller or Travel Site is not active in UFMS Fi   03          0O1PNL
`             nancia ...
`             Obligation Amendment is not processed as Traveller or Travel Site is not active in UFMS Fi   04          0O1PNL
`             nancia ...
`             Obligation Amendment is not processed as Traveller or Travel Site is not active in UFMS Fi   05          0O1PNL
`             nancia ...
`
Gov-Trip.accdb
shieldscoAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
The function works, but as I mentioned the "single quote" is not a single quote, but rather a Chr(96) value (the `  character).

Modify your function to check for that, and then try it again:

Function GetData(DataIn As String) As String

    Dim iStart As Integer
    Dim iEnd   As Integer

    iStart = InStr(1, DataIn, "UFMS:")

    If iStart = 0 Then
        GetData = "NODATA"
    Else


        iEnd = InStr(iStart, DataIn, "'")
        If iEnd = 0 Then
            iEnd = InStr(iStart, DataIn, Chr(96))
        End If

        If iStart > 0 And iEnd > 0 Then
            GetData = Mid(DataIn, iStart + 5, iEnd - iStart - 6)
        Else
            GetData = "NODATA"
        End If
    End If

End Function

Open in new window

Note the change on Line 15 from Chr(34) to Chr(96).

Also, the FIRST instance of ` after your starting marker is just a few characters downstream. If your data is always in this format - that is, if the first marker is always "UFMS:" and the second marker is the SECOND instance of `, then you'll have to modify your function like this:

Function GetData(DataIn As String) As String

    Dim iStart As Integer
    Dim iEnd   As Integer

    iStart = InStr(1, DataIn, "UFMS:")

    If iStart = 0 Then
        GetData = "NODATA"
    Else
        '/ get the first instance of Chr(96)
        iEnd = InStr(iStart, DataIn, Chr(96))
        '/ get the second intance of Chr(96)
        iEnd = InStr(iEnd + 1, DataIn, Chr(96))

        If iStart > 0 And iEnd > 0 Then
            GetData = Mid(DataIn, iStart + 5, iEnd - iStart - 6)
        Else
            GetData = "NODATA"
        End If
    End If

End Function

Open in new window


Now you should be able to use the function in your query
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This function would do it:

Function GetData(DataIn As String) As String

    Dim iStart As Integer
    Dim iEnd   As Integer

    iStart = InStr(1, DataIn, "UFMS:")

    If iStart = 0 Then
        GetData = "NODATA"
    Else


        iEnd = InStr(iStart, DataIn, "'")
        If iEnd = 0 Then
            iEnd = InStr(iStart, DataIn, Chr(34))
        End If

        If iStart > 0 And iEnd > 0 Then
            GetData = Mid(DataIn, iStart + 5, iEnd - iStart - 6)
        Else
            GetData = "NODATA"
        End If
    End If

End Function

Open in new window


You'd use it like this:

Dim sDataOut as String
sDataOut=GetData(Me.SomeField)

Note too those are not single quotes, they're known as the "Grave Accent": http://www.theasciicode.com.ar/ascii-printable-characters/grave-accent-ascii-code-96.html
0
 
shieldscoAuthor Commented:
When I used the function in a query I get NoData -- Expr1: GetData([Error Category])
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
shieldscoAuthor Commented:
Can I use the function in a query
0
 
PatHartmanCommented:
Yes you can use the function in a query.

Put a stop in the code and then step through it to see what is being passed in and how it is being evaluated.
0
 
shieldscoAuthor Commented:
No luck on running from query -- can you give an example Thanks
0
 
shieldscoAuthor Commented:
Can anyone give me an example on how to run the function from a query??
0
 
shieldscoAuthor Commented:
works well -- Thanks
0
All Courses

From novice to tech pro — start learning today.