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, 12:28 PM
Post#21



Posts: 119
Joined: 27-June 13



It's a very simple table one field formatted as short text. I will change the name to sthg else.
Go to the top of the page
 
orange999
post Aug 26 2018, 12:39 PM
Post#22



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


If you tell us the name of the field in your table, and where you want the output, we can provide more focused response.
Go to the top of the page
 
robleh7
post Aug 26 2018, 12:41 PM
Post#23



Posts: 119
Joined: 27-June 13



Orange999 here is the modified testStrip module:
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 Company As String
Dim k As Integer
Set db = CurrentDb

Set rs = db.OpenRecordset("TestSourceData")
Do While Not rs.EOF
Company = Trim(Mid(rs!Company, 4)) ' Get rid of initial number and period
Debug.Print fDropFromStr(Company) '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


And the function you sent:
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 Company As String
Dim k As Integer
Set db = CurrentDb

Set rs = db.OpenRecordset("TestSourceData")
Do While Not rs.EOF
Company = Trim(Mid(rs!Company, 4)) ' Get rid of initial number and period
Debug.Print fDropFromStr(Company) '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
I don't think that the tblRobleh is still in this function right? I search and it s/b removed. Sometimes sthg is right in front of you and you miss it.
These two together is work but it's not running after the changes were made. I could upload a jpg of the table but it's so simple as to be unnecessary, right?

Okay, I have my daughter and granddaughter coming over to go to the pool now, so I'll check out shortly.
Go to the top of the page
 
orange999
post Aug 26 2018, 12:51 PM
Post#24



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


Based on your last post, these lines replace those that you have now.

CODE
Set rs = db.OpenRecordset("TestSourceData")
    Do While Not rs.EOF
        ssample = Trim(Mid(rs!company, InStr(rs!company, " ")))    ' Get rid of initial number and period
        sResult = fDropFromStr(ssample)    'call routine to drop chars from string
        'if you want to do something different with sResult
        'it goes here
        Debug.Print sResult
        rs.MoveNext
    Loop


tblRobleh was my test table name. Nothing to do with your code.
This post has been edited by orange999: Aug 26 2018, 12:53 PM
Go to the top of the page
 
theDBguy
post Aug 26 2018, 02:49 PM
Post#25


Access Wiki and Forums Moderator
Posts: 75,777
Joined: 19-June 07
From: SunnySandyEggo


Hi All,

Just FYI, I did try a little experiment. Please give it a try and let me know what you think.

Attached File  RegExSpeedTestDemo.png ( 77.64K )Number of downloads: 3


I am writing a short blog article to go with this.

Cheers!

PS. Here's the link to the article, if anyone is interested.

This post has been edited by theDBguy: Aug 26 2018, 06:37 PM
Reason for edit: Added link to blog

Attached File(s)
Attached File  theDBguyRegExSpeedTestDemoV1.0.zip ( 525.35K )Number of downloads: 10
 
Go to the top of the page
 
RJD
post Aug 26 2018, 05:43 PM
Post#26


UtterAccess VIP
Posts: 9,751
Joined: 25-October 10
From: Gulf South USA


Hi: Just out of curiosity (and with a few minutes to spare), I wanted to see if I could do this with SQL only and not use VBA (although VBA would probably be faster and be easier to dechiper)... and here is what I did. It assumes that any numbers (of any length) are enclosed in parens. And there need not be numbers there at all - the SQL checks for that. It also assumes that if there are numbers in parens present, the set is followed by a space.

SELECT MyField,
IIf(Instr([MyField],"(")>0,Mid([MyField],InStr([MyField],"("),InStr([MyField],")")-InStr([MyField],"(")+2),"") AS DelSeg1,
Mid(Replace([MyField],[DelSeg1],""),Instr([MyField]," ")+1) AS Rev1,
IIf(InStr([Rev1],"(")>0,Mid([Rev1],InStr([Rev1],"("),InStr([Rev1],")")-InStr([Rev1],"(")+2),"") AS DelSeg2,
Replace([Rev1],[DelSeg2],"") AS Rev2
FROM tblMyRecords;

The process starts by lopping off the ordinal set near the beginning.

This accounts for two paren/number sets. And the final results are in Rev2.

Since the query operates in sequence of fields, the calculations and operations for later operations have the previous operations results available to them.

See the demo attached.

As I said, just curious to see what it would look like ...

HTH
Joe
Attached File(s)
Attached File  RemoveNumbers.zip ( 36.85K )Number of downloads: 9
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
robleh7
post Aug 26 2018, 07:04 PM
Post#27



Posts: 119
Joined: 27-June 13



I have several responses here. Let me address each.

The SQL query doesn't work as expected. Here is my adaptation of it:
SELECT Company,
IIf(Instr([Company],"(")>0,Mid([Company],InStr([Company],"("),InStr([Company],")")-InStr([Company],"(")+2),"") AS DelSeg1,
Mid(Replace([Company],[DelSeg1],""),Instr([Company]," ")+1) AS Rev1,
IIf(InStr([Rev1],"(")>0,Mid([Rev1],InStr([Rev1],"("),InStr([Rev1],")")-InStr([Rev1],"(")+2),"") AS DelSeg2,
Replace([Rev1],[DelSeg2],"") AS Rev2
FROM TestSourceData;

Orange999
YOUR CODE RUNS!!!!!! I checked the immediate window and it outputs just as I want now to get it to output to the table.....next step.

Go to the top of the page
 
RJD
post Aug 26 2018, 07:23 PM
Post#28


UtterAccess VIP
Posts: 9,751
Joined: 25-October 10
From: Gulf South USA


QUOTE
The SQL query doesn't work as expected.

Hmmm ... okay, so what does it do? I also noticed that you did not download my demo, which seems to work for me. You might try that, if you are interested, and see how that works for you. I am curious how my version might be changed to work for you when it works in my demo. Is this a data issue, do you think?

In any case, you seem to have a workable solution, so, good on that, you should probably stick with that.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
orange999
post Aug 26 2018, 09:25 PM
Post#29



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


Thanks for the SpeedTest Demo dBGuy.
I think the times are quite similar for both methods, and quite acceptable for 1.5+ million records.

--------------------
Good luck with your project!
Go to the top of the page
 
robleh7
post Aug 27 2018, 08:13 AM
Post#30



Posts: 119
Joined: 27-June 13



I really need to get this procedure implemented Orange999. It is working smoothly. I see all the records in the immediate window, but there is a limit on what it can show. I need to have them write back to the table. Your help is invaluable here.

if I create a form can I have the module write to the table that way.
Go to the top of the page
 
orange999
post Aug 27 2018, 09:02 AM
Post#31



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



What you are identifying is the need for some code to go here in the routine.

QUOTE
'if you want to do something different with sResult
'it goes here


You could update the record in the current table, or you could insert records into a new table.
Whatever you do, you should first make a copy/backup of your table. That way if something goes awry you haven't lost your data.

I added a new field to my copy of your table revCompany short text.



And adjusted the code as follows:

CODE
Do While Not rs.EOF
        rs.Edit
        ssample = Trim(Mid(rs!company, InStr(rs!company, " ")))    ' Get rid of initial number and period
        sResult = fDropFromStr(ssample)    'call routine to drop chars 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


Seems to work just fine.
I'm off for a few hours to play golf.

Good luck.

--------------------
Good luck with your project!
Go to the top of the page
 
robleh7
post Aug 27 2018, 02:04 PM
Post#32



Posts: 119
Joined: 27-June 13



This adjustment seems to have a bug.
Do While Not rs.EOF
rs.Edit
ssample = Trim(Mid(rs!company, InStr(rs!company, " "))) ' Get rid of initial number and period
sResult = fDropFromStr(ssample) 'call routine to drop chars 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

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

It appears to just update the recordset, but fails for me if it replaces the current code. I think it's the revCompany that's the problem. I check this when I get home.
Go to the top of the page
 
orange999
post Aug 27 2018, 02:57 PM
Post#33



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


Did you add a field named revCompany short text to your table TestSourceData?


Here is the table after running the routine.


Attached File(s)
Attached File  RevCompany.png ( 30.68K )Number of downloads: 5
 

--------------------
Good luck with your project!
Go to the top of the page
 
robleh7
post Aug 27 2018, 03:19 PM
Post#34



Posts: 119
Joined: 27-June 13



Orange999 you are the new all colors to me! You're a genius thx to you the world is a better place. Thx a million
Go to the top of the page
 
robleh7
post Aug 27 2018, 05:59 PM
Post#35



Posts: 119
Joined: 27-June 13



You know after going thru creating the 2nd column and it ran perfectly. I deleted the rows with a delete query and reran it and the now the module runs but doesn't update.
I am step thru to see if anything has gone wrong. Flushing the table couldn't have affected the code. I will take this up tomorrow at work. I will be calling on you again. Orange, stay tuned if you don't mind. I'm sure it's a little error on my part.
Go to the top of the page
 
orange999
post Aug 27 2018, 06:58 PM
Post#36



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


If you deleted with a DELETE query, you may have deleted all your records???
You did make the backup I suggested, right?

--------------------
Good luck with your project!
Go to the top of the page
 
robleh7
post Aug 27 2018, 09:36 PM
Post#37



Posts: 119
Joined: 27-June 13



Yes, yes, yes of course. I do such mindless things Orange. No backup necessary. These records are just tests. , Of course, I forgot my own intentions. So, here is the whole process. Import the records from a xls sheet. Use this VBA module to modify them with the new field. Run a simple duplicate query to clean new table. Create a module using DoCmd.transfersheet method to output it as an xcel file again. Use a delete query to empty the table and import another batch of records to the same table. Repeat the process.
Go to the top of the page
 
robleh7
post Aug 28 2018, 12:07 PM
Post#38



Posts: 119
Joined: 27-June 13



Orange there is one little tweak I am wanting to the code:

Look at this string: Solutia Inc. (007161486) - Akron, USA
I need just Solutia Inc. That is whenever a parenthetical like the occurs drop everything after it.

Code as it stands now, drops what is in the parenthetical string. what needs to be increased?

Also, duplication needs to be addressed but that can done outside of the VBA code.
Go to the top of the page
 
orange999
post Aug 28 2018, 02:00 PM
Post#39



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


Sounds like the requirements are evolving??

What is the desired result of your first example?
1. Test Inc France (4979829) Ltd. Amazon (8929421) Inc.

Please provide some test records and the desired results from processing them.


Is the desired logic to drop everything after the first ")"?
This post has been edited by orange999: Aug 28 2018, 02:07 PM

--------------------
Good luck with your project!
Go to the top of the page
 
robleh7
post Aug 28 2018, 02:50 PM
Post#40



Posts: 119
Joined: 27-June 13



Here is a real record that has been slightly changed in spelling to protect the source
922. PPT AC-Fonces (275477636) - cantoneuse, FRANCE
Anything after the (******) has to be removed including null space.
Another one
923. PPT AC-Fonces (275477636) - cantoneuse, (373938383) FRANCE

any string with parentheticals and numbers remove everything after them.

943. PPT AC-Fonces (gallonez) - cantoneuse, (373938383) FRANCE
the first set set of ()s would not be removed. Only those with numerical data.

Thx, sorry for being indistinct.
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    23rd July 2019 - 01:59 AM