Announcement

Collapse
No announcement yet.

Removing text between parentheses.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Removing text between parentheses.



    Iím using Access and need a VBA solution to remove text between parentheses on a memo field. The text form the save filed looks like this,

    Notes
    (Username and timestamp)
    Some more notes.
    (A different Username and a different timestamp)
    Even more notes.

    I need to send this to another field for exporting a report without the usernames and timestamps.

    Notes
    Some more notes.
    Even more notes.

    Thanks in advanced!

  • #2
    Re: Removing text between parentheses.

    File attached
    Attached Files

    Comment


    • #3
      Re: Removing text between parentheses.

      Thanks! That works fine in Excel but need to translated into Access.
      The fields I am using are “Notes” and “NotesWithoutStamp”

      Private Sub button_Click()
      Dim r As Range
      With CreateObject("VBScript.RegExp")
      .Global = True
      .Pattern = "(^\n+|\([^\)]+\)\n?|\n*$)"
      For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
      r(, 2).Value = .Replace(r.Value, "")
      Next
      End With
      End Sub

      Comment


      • #4


        Re: Removing text between parentheses.

        Is it Access vba?

        You should ask this in Access question board.

        Just a sample
        Code:
        Option Compare Database
        Option Explicit
        
        
        Sub test()
            Dim myDB As DAO.Database
            Dim myRS As DAO.Recordset
            Dim temp As String
            
            Set myDB = CurrentDb
            Set myRS = myDB.OpenRecordset("YourTableNameHere", dbOpenDynaset)
            
            Do Until myRS.EOF
                myRS.Edit
                temp = EditMemo(myRS!Notes)
                myRS!Notes = temp
                temp = EditMemo(myRS!NotesWithoutStamp)
                myRS!NotesWithoutStamp = temp
                myRS.Update
                myRS.MoveNext
            Loop
            myRS.Close
            myDB.Close
        End Sub
        
        
        Function EditMemo(txt As String) As String
            With CreateObject("VBScript.RegExp")
                .Global = True
                .Pattern = "(^\n+|\([^\)]+\)\n?|\n*$)"
                EditMemo = .Replace(txt, "")
            End With
        End Function
        Note:

        You must edit your post and wrap the code with code tag.
        It is as MUST rule here.
        i.e
        [code]
        vba code here
        [/code]
        Last edited by jindon; January 18th, 2013, 10:37. Reason: Typo

        Comment

        Working...
        X