Solved

Strip Out Text from Memo Field

Posted on 2014-01-23
8
440 Views
Last Modified: 2014-01-24
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
Comment
Question by:shieldsco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 85
ID: 39803708
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
 

Author Comment

by:shieldsco
ID: 39803959
When I used the function in a query I get NoData -- Expr1: GetData([Error Category])
0
 

Author Comment

by:shieldsco
ID: 39804091
Can I use the function in a query
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:PatHartman
ID: 39804318
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
 

Author Comment

by:shieldsco
ID: 39804380
No luck on running from query -- can you give an example Thanks
0
 

Author Comment

by:shieldsco
ID: 39806343
Can anyone give me an example on how to run the function from a query??
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39806527
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
 

Author Closing Comment

by:shieldsco
ID: 39806581
works well -- Thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question