UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Using Replace Function To Strip Out Several Characters For One Field, Access 2013    
 
   
robleh7
post Aug 26 2018, 08:09 AM
Post#1



Posts: 119
Joined: 27-June 13



I have a simple issue but may require some extensive use of the Replace function. I have seen questions posted that are related to mine but I think this one is slightly different. I also would like to not create a VBA routine to accomplish this, as it seems quite straightforward.

Here are samples from a field called Names in an access table that needs characters removed.
Name
1. Test Inc France (4979829) Ltd. Amazon (8929421) Inc.
2. Testa Prova Brasil Ltd (4881801) Lmtda
3. Minas Gerais Ltda. (4758921) Uma Banda Nossa Leitura (9002101)
The following characters must be stripped out:

Ordinal numbers starting with 1.
All instances of "(", ")", and the numbers within the parentheses
Any null spaces in these fields both leading and trailing

That's all that needs to be done.

Here is my sample query in SQL that strips out one character and uses an alias

SELECT Replace([Name],"2.","") AS [Name Modified], TestSourceData.Name
FROM TestSourceData;
It is, of course, the very start and not robust enough to accomplish the task.
I have seen a snippet of VBA code in another forum that creates a user-defined public function which contains an array for each character to be removed and loop with upper and lower bounds. A query then calls this function to do the character reduction. It seems like an ideal solution. My problem was how to get the query to call the function. It seems way too much do also. There s/b a simpler solution for a task so minor. Here is the code anyway. It's not my own but since it was shared publicly I don't think there is anything wrong with showing it in this forum:
First the function:

Public Function SuperReplace(ByRef field As String, ByVal ReplaceString As String) As String
' Size this as big as you need... it is zero-based by default'
Dim ReplaceArray(3) As String

'Fill each element with the character you need to replace'
ReplaceArray(0) = "#"
ReplaceArray(1) = ")"
ReplaceArray(2) = "("
ReplaceArray(3) = " "

Dim i As Integer
For i = LBound(ReplaceArray) To UBound(ReplaceArray)
field = Replace(field, ReplaceArray(i), ReplaceString)
Next i

SuperReplace = field
End Function

And now the query:

SELECT SuperReplace(Name,"") AS ModifiedName
FROM TestSourceData;

Of course, I changed the query to show my test table in the FROM clause. I changed the array also to use the characters I want to remove.

Again, is there a more direct way to do this? The table shown is always in the pattern given. It can contain thousands of rows (in excel, what else right?). There is only that one field (Names). This is for my job if you haven't already guessed. It seemed a ridiculous task to use all manner of functions in excel to clean these rows up to me.

I am wondering if nesting REPLACE might accomplish the task.

Any and all effective suggests are welcomed and appreciated.
This post has been edited by robleh7: Aug 26 2018, 08:11 AM
Go to the top of the page
 
orange999
post Aug 26 2018, 08:26 AM
Post#2



Posts: 1,994
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Can you show us what exactly you would consider as the "correct" result of your sample data?
Go to the top of the page
 
theDBguy
post Aug 26 2018, 08:26 AM
Post#3


UA Moderator
Posts: 76,824
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just driving by... Sure, you can nest the Replace() function, but I’m not sure if there’s a limit to how many levels you’re allowed to nest it. What’s wrong with using VBA for a simple task?
Go to the top of the page
 
robleh7
post Aug 26 2018, 09:26 AM
Post#4



Posts: 119
Joined: 27-June 13



Test Inc France Ltd. Amazon Inc.
This would be a correct display of the first Name in my example.
i.e. no leading spaces, no number enumeration, no parentheticals with numbers within.

BTW the current procedure at my job is using Excel with the functions TRIM, LEFT, RIGHT, ISNUMBER, SEARCH all used in succession to accomplish this simple reduction. It is unbelievable how much time it times to do this. I won't even describe the insanity of how these functions are applied.


This post has been edited by robleh7: Aug 26 2018, 09:28 AM
Go to the top of the page
 
robleh7
post Aug 26 2018, 09:33 AM
Post#5



Posts: 119
Joined: 27-June 13



DBguy there is nothing wrong with using VBA for simple tasks. Don't misunderstand me, I just think it's overkill in this case. But since you mentioned it. Look at the code I sent and tell me how can I get it to work with the sample query.

I can't embed the SQL query in the module with the function, now can I? If I run the query alone, it errors when it calls the function SuperReplace function. error msg: Undefined function 'SuperReplace' in expression. This means to me that the query is not calling the function b/c it is not where it is expected to be.
Go to the top of the page
 
orange999
post Aug 26 2018, 09:34 AM
Post#6



Posts: 1,994
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


A function to drop characters form a string seems a reasonable approach.

Here is some code i modified to drop characters "()0123456789."
I'm not sure if you want to drop the "." --If not just delete that line in the function that checks for the period.

CODE
Function fDropFromStr(ByVal strInString As String) As String

'(Q) How to Drop any of these characters from a string .1234567890()
'(A) Use the following function.
'from code originally presented by Dev Ashish
'************ Code Start **********

    Dim lngLen As Long, strOut As String
    Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        'The next statement will drop any of these chars from output
        '40 is (
        '41 is )
        '46 is .
        '48 thru 57  are 0 thru 9
        If Asc(strTmp) = 40 Or _
           Asc(strTmp) = 41 Or _
           Asc(strTmp) = 46 Or _
           (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
        Else
            strOut = strOut & strTmp
        End If
    Next i
    fDropFromStr = Trim(strOut)
End Function


This was my test based on your sample data.
CODE
' ----------------------------------------------------------------
' Procedure Name: testStrip
' Purpose: Code to show dropping specific characters from a given string
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 26-Aug-18
' ----------------------------------------------------------------
Sub testStrip()
    On Error GoTo testStrip_Error
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sResult As String
    Dim ssample As String
    Dim k As Integer
    Set db = CurrentDb

    Set rs = db.OpenRecordset("tblRobleh")
    Do While Not rs.EOF
        ssample = Trim(Mid(rs!sample, 4)) ' Get rid of initial number and period
        Debug.Print fDropFromStr(ssample) 'call routine to drop chars from string --I include the "." but wasn't sure
        rs.MoveNext
    Loop
    
    On Error GoTo 0
    Exit Sub

testStrip_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testStrip, line " & Erl & "."

End Sub


You could find the first space from the left of your record, then only deal with the remainder of the string if you numbering goes above 9.
Hope this is helpful.
Go to the top of the page
 
robleh7
post Aug 26 2018, 09:45 AM
Post#7



Posts: 119
Joined: 27-June 13



DBguy there is nothing wrong with using VBA for simple tasks. Don't misunderstand me, I just think it's overkill in this case. But since you mentioned it. Look at the code I sent and tell me how can I get it to work with the sample query.

I can't embed the SQL query in the module with the function, now can I? If I run the query alone, it errors when it calls the function SuperReplace function. error msg: Undefined function 'SuperReplace' in expression. This means to me that the query is not calling the function b/c it is not where it is expected to be.
Go to the top of the page
 
robleh7
post Aug 26 2018, 09:52 AM
Post#8



Posts: 119
Joined: 27-June 13



Orange999, I could test this by creating a control button and associating the module with it, and replacing tblRobleh with my actual table?

I really wanna try this. It may be the simple quick solution I'm seeking. Back in a minute, washing clothes.

I am attempted to write you're the new Black1000, but maybe not, the previous as a quote so it's not actually writing it.
Go to the top of the page
 
theDBguy
post Aug 26 2018, 09:55 AM
Post#9


UA Moderator
Posts: 76,824
Joined: 19-June 07
From: SunnySandyEggo


Hi again!

For something like this, I like to use regular expressions. So, I took the code I had from here and modified it a bit to fit your requirement.

CODE
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

Here's an example result in the Immediate Window.

CODE
?extractemailaddress("Test Inc France (4979829) Ltd. Amazon (8929421) Inc.")
Test Inc France Ltd. Amazon Inc.

Not sure why you're getting an error in your original code. We can't see what you did. An undefined function could mean you didn't save the module properly. For one thing, I noticed you were using "Name" as the name of your field or column. If so, "name" is a reserved word in Access. It requires special handling if you want to continue using it as the name of your field or column. Otherwise, best practice is to just avoid using reserved words for column/field names (or any other objects, for that matter).

Just my 2 cents...
Go to the top of the page
 
robleh7
post Aug 26 2018, 10:13 AM
Post#10



Posts: 119
Joined: 27-June 13



I did forget NAME is a reserved word! Okay, this might be good to use. I see in the Immediate that it does whack what I want gone. I gotta try this. BRB (actually in about an hour) Thx DBguy and Orange999.
Go to the top of the page
 
robleh7
post Aug 26 2018, 10:40 AM
Post#11



Posts: 119
Joined: 27-June 13



Okay Or999, you seem to have the best solution. So, I'm concentrating on your code.

Now, tell while go back the laundry. How are you running this code?

Is it all contained in one module?
I wanna try it.
Go to the top of the page
 
theDBguy
post Aug 26 2018, 10:46 AM
Post#12


UA Moderator
Posts: 76,824
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Good luck!

Re: "Okay Or999, you seem to have the best solution. So, I'm concentrating on your code."

I was actually hoping you could help us find a definite answer to that question. And by "best," I was just thinking about speed. I thought you might be willing to perform some speed test using your data ("thousands of rows") just to let us know which approach was faster.

I was just curious. If not, maybe next time.

Cheers!
Go to the top of the page
 
robleh7
post Aug 26 2018, 10:54 AM
Post#13



Posts: 119
Joined: 27-June 13



Shrek, I mean DBguy, I'm not dumping your solution either. I just wanna try this at home fast, and then do it at work tomorrow. I have to until next friday to replace the incredibly lugubrious (I knew I'd get to use the word one day!) excel solution.
Tell me how you're running your code too. I wanna just have this in one module and run it all at once.
Go to the top of the page
 
orange999
post Aug 26 2018, 11:01 AM
Post#14



Posts: 1,994
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I took your example data and create a 2 field table (tblRobleh)
ID autonumber
Sample (your test name/string)

I did not do volume testing nor timing.
My intent was to show a way to drop certain characters from a string.
I adapted this function from another function that I have used for many years.
I think the code and looping is quite simple. It isn't complex coding. I did not think your request for the fastest solution.
You could certainly try various solutions with consistent volume of data and tell us your timing/conclusions.


I did not use a form--don't believe it is required (necessarily), but don't know details of your needs.
I read the table, record by record.
I removed the leftmost 3 characters and TRIMmed the remainder into a variable sSample.
Then check each sSample, char by char, and drop anything in your exclude list.
I simply Printed the sResult to the immediate window, but you could insert into a new table or whatever your requirement is.

I see there where interim posts before I posted previously. You don't want to drop "." within the "name", so you can remove the
CODE
  Asc(strTmp) = 46 Or _


Like DbGuy, I have used Regex to remove characters from data also.
This post has been edited by orange999: Aug 26 2018, 11:09 AM
Go to the top of the page
 
robleh7
post Aug 26 2018, 11:11 AM
Post#15



Posts: 119
Joined: 27-June 13



Actually, DBguy your code might be better suited, I'm not sure.

Orange999 see my comment on applying the user defined function fDropFromStr?
Go to the top of the page
 
theDBguy
post Aug 26 2018, 11:33 AM
Post#16


UA Moderator
Posts: 76,824
Joined: 19-June 07
From: SunnySandyEggo


Hi all,

I have no doubt in my mind both approach should do the job. I was just curious as to which one would do it faster, just in case someone ask. Of course, faster doesn't necessarily mean "best" either. When I get a chance, I might perform the experiment myself, just to satisfy my own curiosity.

robleh, can you please tell us the end goal here? Are you trying to clean up your data? Meaning, do you want to throw away the old data and simply end up with a clean table in the end?
Go to the top of the page
 
robleh7
post Aug 26 2018, 11:49 AM
Post#17



Posts: 119
Joined: 27-June 13



Okay, we're getting close now. I'm getting error 3265 when I run testStrip which calls the function fDropFromStr(ssample). It could be the (ssample).
Go to the top of the page
 
orange999
post Aug 26 2018, 11:57 AM
Post#18



Posts: 1,994
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Do you have a variable named sSample?

Can you post the code you are using?
It would also be helpful if we knew your table structure.

Here is my latest code --
--removed the need to Drop a period/decimal point "."
--allowed for various numbers beyond single digit at left of your "name".

CODE
' ----------------------------------------------------------------
' Procedure Name: testStrip
' Purpose: Code to show dropping specific characters from a given string
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 26-Aug-18
' ----------------------------------------------------------------
Sub testStrip()
    On Error GoTo testStrip_Error
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sResult As String
    Dim ssample As String
    Dim k As Integer
    Set db = CurrentDb

    Set rs = db.OpenRecordset("tblRobleh")
    Do While Not rs.EOF
        ssample = Trim(Mid(rs!sample, InStr(rs!sample, " "))) ' Get rid of initial number and period
        Debug.Print fDropFromStr(ssample) 'call routine to drop chars from string
        rs.MoveNext
    Loop
    
    On Error GoTo 0
    Exit Sub

testStrip_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testStrip, line " & Erl & "."

End Sub


This is the revised function -- it no longer drops "."

CODE
Function fDropFromStr(ByVal strInString As String) As String

'(Q) How to Drop any of these characters from a string 1234567890()
'(A) Use the following function.
'from code originally presented by Dev Ashish
'************ Code Start **********

    Dim lngLen As Long, strOut As String
    Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        'The next statement will drop any of these chars from output
        '40 is (
        '41 is )
        '46 is .
        '48 thru 57  are 0 thru 9
        
        If Asc(strTmp) = 40 Or _
           Asc(strTmp) = 41 Or _
           (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
        Else
            strOut = strOut & strTmp
        End If
    Next i
    fDropFromStr = Trim(strOut)
End Function


This is my test data (yours + a record with long number)
CODE
id    sample
1    1. Test Inc France (4979829) Ltd. Amazon (8929421) Inc.
2    2. Testa Prova Brasil Ltd (4881801) Lmtda
3    3. Minas Gerais Ltda. (4758921) Uma Banda Nossa Leitura (9002101)
4    45834. This is a sample (324567) Inc. Ltd.


This is the output to the immediate window.

CODE
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 post has been edited by orange999: Aug 26 2018, 12:23 PM
Go to the top of the page
 
robleh7
post Aug 26 2018, 12:11 PM
Post#19



Posts: 119
Joined: 27-June 13



Yes, I'll give you everything.
Here is the procedure module which you created:
Option Compare Database
Function fDropFromStr(ByVal strInString As String) As String

'(Q) How to Drop any of these characters from a string .1234567890()
'(A) Use the following function.
'from code originally presented by Dev Ashish
'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

lngLen = Len(strInString)
strOut = ""
For i = 1 To lngLen
strTmp = Left$(strInString, 1)
strInString = Right$(strInString, lngLen - i)
'The next statement will drop any of these chars from output
'40 is (
'41 is )
'46 is .
'48 thru 57 are 0 thru 9
If Asc(strTmp) = 40 Or _
Asc(strTmp) = 41 Or _
Asc(strTmp) = 46 Or _
(Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
Else
strOut = strOut & strTmp
End If
Next i
fDropFromStr = Trim(strOut)
End Function
' ----------------------------------------------------------------
' Procedure Name: testStrip
' Purpose: Code to show dropping specific characters from a given string
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 26-Aug-18
' ----------------------------------------------------------------
Sub testStrip()
On Error GoTo testStrip_Error
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sResult As String
Dim ssample As String
Dim k As Integer
Set db = CurrentDb

Set rs = db.OpenRecordset("TestSourceData")
Do While Not rs.EOF
ssample = Trim(Mid(rs!sample, 4)) ' Get rid of initial number and period
Debug.Print fDropFromStr(ssample) 'call routine to drop chars from string --I include the "." but wasn't sure
rs.MoveNext
Loop

On Error GoTo 0
Exit Sub

testStrip_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testStrip, line " & Erl & "."

End Sub

Here the function you created:
Option Compare Database
Function fDropFromStr(ByVal strInString As String) As String

'(Q) How to Drop any of these characters from a string .1234567890()
'(A) Use the following function.
'from code originally presented by Dev Ashish
'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

lngLen = Len(strInString)
strOut = ""
For i = 1 To lngLen
strTmp = Left$(strInString, 1)
strInString = Right$(strInString, lngLen - i)
'The next statement will drop any of these chars from output
'40 is (
'41 is )
'46 is .
'48 thru 57 are 0 thru 9
If Asc(strTmp) = 40 Or _
Asc(strTmp) = 41 Or _
Asc(strTmp) = 46 Or _
(Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
Else
strOut = strOut & strTmp
End If
Next i
fDropFromStr = Trim(strOut)
End Function
' ----------------------------------------------------------------
' Procedure Name: testStrip
' Purpose: Code to show dropping specific characters from a given string
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 26-Aug-18
' ----------------------------------------------------------------
Sub testStrip()
On Error GoTo testStrip_Error
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sResult As String
Dim ssample As String
Dim k As Integer
Set db = CurrentDb

Set rs = db.OpenRecordset("TestSourceData")
Do While Not rs.EOF
ssample = Trim(Mid(rs!sample, 4)) ' Get rid of initial number and period
Debug.Print fDropFromStr(ssample) 'call routine to drop chars from string --I include the "." but wasn't sure
rs.MoveNext
Loop

On Error GoTo 0
Exit Sub

testStrip_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testStrip, line " & Erl & "."

End Sub

My table in Access is TestDataSource and that is what I changed the openrecordset to. And now that I write this I see ssample = name and the dim statement s/b name also right?

Go to the top of the page
 
orange999
post Aug 26 2018, 12:17 PM
Post#20



Posts: 1,994
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


We need to know the design of "TestSourceData".

Can you go to Table Design view and capture a jpg or png of the screen. It should show the fields names and datatypes.

What are the fields involved?
Do you have more sample data?

Name is a reserved word in Access.

What exactly is the name of the field in table "TestSourceData" that you are working with?
This post has been edited by orange999: Aug 26 2018, 12:21 PM
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 04:52 AM