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 28 2018, 03:10 PM
Post#41



Posts: 119
Joined: 27-June 13



Orange on 2nd thought don't bother with the last comment. All parentheticals s/b removed after all. my apologies.
Go to the top of the page
 
orange999
post Aug 28 2018, 03:13 PM
Post#42



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


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

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



Posts: 119
Joined: 27-June 13



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.
Go to the top of the page
 
orange999
post Aug 28 2018, 06:53 PM
Post#44



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


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.

--------------------
Good luck with your project!
Go to the top of the page
 
robleh7
post Sep 29 2018, 03:19 PM
Post#45



Posts: 119
Joined: 27-June 13



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



Go to the top of the page
 
robleh7
post Jun 28 2019, 07:50 PM
Post#46



Posts: 119
Joined: 27-June 13



After all this time and new job would you believe it, but now I have something quite similar that this module would solve. now I want to strip out characters from an integer field.

So, here is the story in brief. I would post any code yet. I think the existing module can be reworked to accomplish this task.

I have a data source which is an open field called ActionID it's formatted as a string in this database (XFMEA). It's linked to Access and the current analyst has been using an expression to cast the field to be numeric. This doesn't always work because many odd characters are input to XFMEA. spaces, letters, all manner of diacritical marks, :, etc. So, I began to wonder if this procedure could be adapted to remove specific characters from a field with numbers. The field is set to data type Long Text I think, will have to verify that at work on monday. But in this case we want to strip all kinds of non-numeric characters from the numbers and place them in a second field. Obviously, the code would have to be reworked. Here is a representative example:


3872938l:279r

The non-numeric characters would need to be removed.

This would most likely be accomplished with a loop of some kind, run in series. By which, I mean the loop would be run in stages, remove each character type on successive passes.

Go to the top of the page
 
RJD
post Jun 28 2019, 08:56 PM
Post#47


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


Hi:
QUOTE
I began to wonder if this procedure could be adapted to remove specific characters from a field with numbers. ... But in this case we want to strip all kinds of non-numeric characters from the numbers and place them in a second field.

QUOTE
The non-numeric characters would need to be removed.

I'm not real clear on your requirements. Kinda sounds like you want the numbers in one field and the non-numbers in another field. This should be much simpler than the code previously worked. I did both in the demo attached. See if this is close to what you are trying to do.

Also, you should avoid tagging onto an old thread. Fewer folks will bother to wade through all the previous posts to see what prompted the last post. Better to start a new thread and reference the old thread if necessary.

Let us know if the attached demo is going in the direction you want, or you can provide us with more explanation if not.

Here's the code to get the numerics part ...

CODE
Public Function GetNumbers(F As String) As String

Dim Pos As Integer
Pos = 1
Do Until Pos > Len(F)
  GetNumbers = GetNumbers & IIf(IsNumeric(Mid(F, Pos, 1)), Mid(F, Pos, 1), Null)
  Pos = Pos + 1
Loop

End Function


HTH
Joe
Attached File(s)
Attached File  NumericsFromAField.zip ( 21.56K )Number of downloads: 3
 

--------------------
"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
 
RJD
post Jun 29 2019, 04:44 PM
Post#48


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


...and an afterthought. Did you just want the numbers up to the first non-number - or as I did it, ALL numbers, anywhere they might occur. Easy mod to the code (and easier code) to get the numbers up to the first non-number.

HTH
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
 
robleh7
post Jun 29 2019, 06:53 PM
Post#49



Posts: 119
Joined: 27-June 13



You are right, I should have started a new thread.

I want non-numbers in one field and numbers in a new field

So, a field with string objects (ex: 79792A:%23) transformed into strictly numeric values.

diagrammatically:

field 1 = string object from the XFMEA db
field 2 = number transformed from the field 1
Important to notify in the access db, fields are formatted as text.

I really apologize for tacking on to an old thread, should have realized that was not smart. But, now that I think about it, I thought that if you follow the discussion you get an email notifying you when new posts are made. But, still you're right a new thread referencing the old one is the best procedure to follow.

I'll step thru the function and see what I get in the immediate window. Thx Joe, you'll hear from me again.

BTW, notification of posts in this forum to me of my discussions seems to have stopped. I'll have to check my notification settings.
Go to the top of the page
 
RJD
post Jun 29 2019, 07:02 PM
Post#50


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


Hi again: I'm still not completely clear on your requirement, since you didn't give a solution to the example you posted. All numbers, numbers up to the first non-number, all characters with non-numbers converted to numbers. I assumed the first alternative. Test the demo I attached and let us know.

HTH
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
 
robleh7
post Jun 30 2019, 07:07 PM
Post#51



Posts: 119
Joined: 27-June 13



Yes, yes this is achieving what I want. Now the function needs to be wrap inside a sub to gen the new field w/o any non-numeric characters. The strip out of the extraneous characters themselves is not needed. Just the first two fields.

So, my task is to work this into a sub that takes the table and creates a new field w/o the non-numeric characters. And the current routine really already does that.

There will be a minor issue about spaces, a space in between one string of numbers can mean two actionid numbers but let's consider that now.

Go to the top of the page
 
RJD
post Jun 30 2019, 08:34 PM
Post#52


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


Hi again: In many cases, there is really no reason to update the table with the new numeric value. You can always get that value with the query. But, if it IS needed for some reason, you can pre-define the field in the table, then use an UPDATE query to load the values. See the revised demo attached. Look at the table, run the query, then look at the table again.

QUOTE
There will be a minor issue about spaces, a space in between one string of numbers can mean two actionid numbers but let's consider that now.

Did you mean NOT consider that now? If you do need to consider that, the coding is pretty straight-forward as a mod to the existing code. Again, add the second numeric field manually to the table and the code along with the query can update two fields at the same time.

Give this a try, and let us know if we can then be of assistance.

HTH
Joe
Attached File(s)
Attached File  NumericsFromAField_Rev1.zip ( 21.14K )Number of downloads: 2
 

--------------------
"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
 
3 Pages V < 1 2 3


Custom Search


RSSSearch   Top   Lo-Fi    23rd July 2019 - 07:27 AM