Solved

Strip Out Text from Memo Field

Posted on 2014-01-23
8
428 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
  • 5
  • 2
8 Comments
 
LVL 84
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 35

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 84

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

776 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