Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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
0
shieldsco
Asked:
shieldsco
  • 5
  • 2
1 Solution
 
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
 
shieldscoAuthor Commented:
Can I use the function in a query
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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
 
shieldscoAuthor Commented:
works well -- Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now