[X]My Assistant

[robleh7]

Orange on 2nd thought don't bother with the last comment. All parentheticals s/b removed after all. my apologies.

[orange999]

If this is acceptable
PPT AC-Fonces (gallonez) - cantoneuse, FRANCE
from
943. PPT AC-Fonces (gallonez) - cantoneuse, (373938383) FRANCE

Then it is better to use the routine suggested by theDBGuy.

Where my routine dropped specific characters, his is looking for "pattern" and will single out numbers within brackets
and leave alphabetics within brackets.

I can assist in setting up his function if he is not online/available.

I noticed you have gone offline.
I will adjust the routine I gave to use DBguy's function

Here is the revised test routine.

CODE

' ----------------------------------------------------------------
' Procedure Name: testDBguy
' Purpose: Routine to test removing numbers within  brackets
' Procedure Kind: Sub
' Procedure Access: Public
' Author: theDBGuy /adjusted Jack
' Date: 28-Aug-18
' ----------------------------------------------------------------
Sub testDBguy()
Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim iBracket As Integer
    Dim sResult As String
    Dim ssample As String
    Set db = CurrentDb

    Set rs = db.OpenRecordset("TestSourceData")

    Do While Not rs.EOF
        rs.Edit
        ssample = Trim(Mid(rs!company, InStr(rs!company, " ")))    ' Get rid of initial number and period
        
        sResult = ExtractEmailAddress(ssample)    'call routine to drop numbers within brackets from string
        'if you want to do something different with sResult
        'it goes here
        rs!revCompany = sResult
        rs.Update
        Debug.Print sResult
        rs.MoveNext
    Loop

    On Error GoTo 0
    Exit Sub

End Sub

Here is the function theDBguy provided earlier in the thread.

CODE

'fromdbGuy
' ----------------------------------------------------------------
' Procedure Name: ExtractEmailAddress
' Purpose: To remove a pattern of characters from a string. Provided by theDBguy
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strData (String):
' Parameter strDelim (String):
' Return Type: String
' Author: Jack
' Date: 28-Aug-18
' ----------------------------------------------------------------
Public Function ExtractEmailAddress(strData As String, _
    Optional strDelim As String = ";") As String
'http://accessmvp.com/thedbguy
'6/28/2015
'Extracts any email address found within a string
'regex email pattern source:
'http://www.regular-expressions.info/email.html

Dim regEx As Object
Dim regExMatch As Object
Dim var As Variant
Dim strEmail As String

Set regEx = CreateObject("VBScript.RegExp")
strEmail = strData

With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "\([0-9]*\)"
    Set regExMatch = .Execute(strData)
    For Each var In regExMatch
        strEmail = Replace(strEmail, var, "")
    Next
End With

ExtractEmailAddress = Replace(strEmail, "  ", " ")

Set regEx = Nothing
Set regExMatch = Nothing

End Function

These are the results of using my test data with theDBguy's routine

PPT AC-Fonces (gallonez) - cantoneuse, FRANCE
Test Inc France Ltd. Amazon Inc.
Testa Prova Brasil Ltd Lmtda
Minas Gerais Ltda. Uma Banda Nossa Leitura
This is a sample Inc. Ltd.
This is another sample company. With some STRANGE CHARS #$%^&*


and the updated table:

CODE

id    company    revCompany
1    1. Test Inc France (4979829) Ltd. Amazon (8929421) Inc.    Test Inc France Ltd. Amazon Inc.
2    2. Testa Prova Brasil Ltd (4881801) Lmtda    Testa Prova Brasil Ltd Lmtda
3    3. Minas Gerais Ltda. (4758921) Uma Banda Nossa Leitura (9002101)    Minas Gerais Ltda. Uma Banda Nossa Leitura
4    45834. This is a sample (324567) Inc. Ltd.    This is a sample Inc. Ltd.
5    352261. This is another sample company. With some STRANGE CHARS #$%^&*    This is another sample company. With some STRANGE CHARS #$%^&*
6    943. PPT AC-Fonces (gallonez) - cantoneuse, (373938383) FRANCE    PPT AC-Fonces (gallonez) - cantoneuse, FRANCE

Good luck.
This post has been edited by orange999: Aug 28 2018, 03:48 PM

[robleh7]

I have got to get this clarified before I insert this change. Right now, I can live with what your code does and remove leftovers with criteria. I might write you once more b4 the end of this week. I think as it stands now, the code you gave me is sufficient.

[orange999]

TheDBguy code I gave uses my test data and his function. It will remove "( with numbers here)". It doesn't remove things after the bracketed value.
But his will leave "(letters here)". My code does not handle the "(letters here)"

I can help, but we really need the criteria.
And as mentioned before, some real samples including what you need as a result helps clarify things.

[robleh7]

I was going to make a new post for the question below. But on further thought, think that shouldn't be necessary. Why not just append to this existing thread.

The help I got on this database was inestimable. Now, I've moved on to proposing another issue, not in this dB, but in one that is used at work to produce search strings. Here is a brief description of what I am trying to do. This like many of my previous posts is a fairly simple modification.

Previously Orange999 provided me with a wonderful VBA module that stripped out characters from a table in the names field by generating a modified second column on the table. This is important to mention because now, I'm modifying the way users query these two fields. The two fields are called Names, Names1 (I know a reserved word) or some variant of those terms. There is a GUI query set up in QBE mode, that a user inputs a search string on criteria lines. The user inputs the SAME criteria to each field and thus uses the OR operator to put these strings together. So, here is my first request:

Since the same search string is used on each line of the criteria I would like to have the string pasted to the second criteria when it is typed into the first criteria.

I have made the criteria into a parameter input with the wildcard built in. The following string all should recognize:
Like "*" & [Enter Search String ] & "*"

I don't want a user to have to input their search string twice. I want it so that once it's entered to the first prompt it is copied pasted to the second criteria line.
I have started a GUI macro that will run the query in a sequence. I plan to have it query for the first search string, then show a msgbox and give the user a chance to redo the query on those results using requery.

The following methods should allow me to copy and paste input parameter string in the first criteria into the second:
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPaste

They work fine with some objects like fields, but I not sure they can be used for criteria strings.

The problem is I can't figure out putting it all together. I know the GUI macro builder can start the process. So, the overall structure would be as follows:

Openquery
Runcode (here is where the two methods above would be used to get the search string the user inputs into both criteria lines)
Msgbox to allow the user to requery the results
Requery (or perhaps Redo)
Then end