Repeat Replace Function

[welcomesui@gmail.com]

Good Morning All,

I have small requirement with Replace function, as per my understanding Replace function is used to replace a text in a filed.

My requirement is if one filed contians more than 10 words to replace at once, how to repeat the sam function for that filed to give output in another filed.

Example
Field1 Exp1: NewName
A, B,C,D,E,F Apple,Bat,Cat,Dog,Eat,Frog.


can any once suggest how to repeat this function

Regards,
Hari

 

[vbaInet]

Replace it with what?

You should be saving the values into another field. This is bad practice.

What character are you using as the delimeter?

 

[welcomesui@gmail.com]

Replace it with what?

You should be saving the values into another field. This is bad practice.

What character are you using as the delimeter?

Hi,

I am useing a query with Replace Function, one field contians data and another filed contians (Expr1:Replace(Field1,"string","replace string")), so I want to repeat this function for different words that contian in Filed1 in each row of data, all the replace values placed at Expr1.

Hope you understand.

Regards,
Hari

 

[vbaInet]

Hari,

I understand what you want to do that's why I asked these two questions:

Replace it with what?

What character are you using as the delimeter?

 

[welcomesui@gmail.com]

Hari,

I understand what you want to do that's why I asked these two questions:

Hi,
Filed1 is text string, it contains text with comma seperator

Example
Field1
ICICI Bank, ADR, pfd
as in next filed
Expr1:Replace(Filed1,"ADR","American Deposistory Receipt")
Expr1:Replace(Field1,"pfd","Preferred Stocks")


Regards,
Hari

 

[vbaInet]

If you want to replace American Deposistory Receipt with ADR and Preferred Stocks with pfd... etc... you need to normalize instead.

You should have a table with three fields,

ID (Autonumber) - PK
FieldName (Text)
FieldAbbreviation (Text)

Obviously use appropriate names for the fields in red. The FieldName will contain text like American Deposistory Receipt, Preferred Stock and FieldAbbreviation will contain ADR, pdf etc... the abbreviation of the word.

Then in your table, replace American Deposistory Receipt and Preferred Stocks with pfd... etc ... with the ID field from the new table. Change the data type to Number of the field in your original table to Number, save and close. Then link that field to your new table

 

[welcomesui@gmail.com]

yes.. I did the same thing as below.

Created table with three coumns table name Abb
ID. PK
FieldName -American Dep Receipt
FieldAbb -ADR

My main table contians five columns as ADR
ID. PK
Filed1
Field2
Field3
Field4 - Contians Name

Created new query
Added two tables
Inner JOIN with ID Number

Selected cloumns
colunn1 :ADR.Filed4
cloumn2: Expr1: Relace (Field4,abb.FieldAbb,abb.FieldName)

in Expr1 I am getting #Error. is i am doing correct.


Regards,
Hari

 

[vbaInet]

yes.. I did the same thing as below.

Created table with three coumns table name Abb
ID. PK
FieldName -American Dep Receipt
FieldAbb -ADR

The Abb table will contain records like this:

ID   |   FieldName               |   FieldAbb
----------------------------------------------
1        American Dep Receipt        ADP
2        Preferred Stocks            PFD

My main table contians five columns as ADR
ID. PK
Filed1
Field2
Field3
Field4 - Contians Name

Created new query
Added two tables
Inner JOIN with ID Number

Selected cloumns
colunn1 :ADR.Filed4
cloumn2: Expr1: Relace (Field4,abb.FieldAbb,abb.FieldName)

in Expr1 I am getting #Error. is i am doing correct.

Now here you don't need to do a Replace. Just drop the FieldAbb field in a column in the query.

 

[welcomesui@gmail.com]

The Abb table will contain records like this:

ID   |   FieldName               |   FieldAbb
----------------------------------------------
1        American Dep Receipt        ADP
2        Preferred Stocks            PFD

Now here you don't need to do a Replace. Just drop the FieldAbb field in a column in the query.

Hi,

as per my understanding if the Filed4 contains only ADR it replace to ADR

I hope we have a little bit of miscommunication about understanding.

in my ADR table Filed4 not only contians ADR, it contians along with other Text Also
Adr

ID   |   Field4                     |   Required Output
----------------------------------------------
1        ICICI Bank,ADR,pfd        ICICI Bank, American Dep Recept, prefrred Stocks
2        Bank of America,pfd       Bankof America, Preffred Stocks

I did the same thing.. it is not giving output.

can I post sample data base.


Regards
Hari

 

[vbaInet]

Alright, I see what you mean now. How many different abbreviations do you have?

 

[welcomesui@gmail.com]

Alright, I see what you mean now. How many different abbreviations do you have?

it's around 20-30

 

[vbaInet]

Ok, here's some code (untested):

Public Function GetAbb(strFull As Variant) As Variant
    Dim rs As DAO.Recordset
    Dim strSQL As String, strAbbs As String
    
    If Len(strFull & vbNullString) = 0 Then
        Exit Function
    End If
    
    strSQL = "SELECT [COLOR=Red]FieldAbb[/COLOR] " & _
             "FROM [COLOR=Red]Abb[/COLOR] " & _
             "WHERE [COLOR=Red]FieldName [/COLOR]IN ('" & Replace(strFull, ",", "','") & "');"
    
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    Do While Not rs.EOF
        strAbbs = strAbbs & rs![COLOR=Red]FieldAbb[/COLOR] & ","
        rs.MoveNext
    Loop
    
    GetAbb = Left(strAbbs, Len(strAbbs) - 1)
    
    Set rs = Nothing
End Function

Amend the red bits.

To call:

GetAbb([Field4])

 

[MSAccessRookie]

Everything that has been said here is very good advice. Normalization is by far the best approach to take in resolving your problem. However, if you are unable to follow the advice, there are some answers that might work for you.

1. NESTED Replace() Functions

The syntax for the replace() command is as follows:

Replace ( <String to Search>, <SubString to Find>, <Replacement Value> )

http://www.techonthenet.com/access/functions/string/replace.php

Note that there are also three addition (optional) parameters (start, count, and compare) that do not apply to this discussion.

It should be noted that since the output of the Replace() Function is a String, and its first parameter is also a String, you can nest replace() Functions. This approach is really only useful when the level of nestings is known in advance and is relatively small.

UDF OPTION

You can create a Recursive VBA Function similar to a Factorial Function that scans a string, makes a single substitutions, calls itself again with the modified String as the parameter, and ends when there are no more substitutions to make.

Again, I would like to state that everything that has been said here is very good advice, and that normalization is by far the best approach to take in resolving your problem.

 

[welcomesui@gmail.com]

Ok, here's some code (untested):

Public Function GetAbb(strFull As Variant) As Variant
    Dim rs As DAO.Recordset
    Dim strSQL As String, strAbbs As String
 
    If Len(strFull & vbNullString) = 0 Then
        Exit Function
    End If
 
    strSQL = "SELECT [COLOR=red]FieldAbb[/COLOR] " & _
             "FROM [COLOR=red]Abb[/COLOR] " & _
             "WHERE [COLOR=red]FieldName [/COLOR]IN ('" & Replace(strFull, ",", "','") & "');"
 
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
    Do While Not rs.EOF
        strAbbs = strAbbs & rs![COLOR=red]FieldAbb[/COLOR] & ","
        rs.MoveNext
    Loop
 
    strAbbs = Left(strAbbs, Len(strAbbs) - 1)
 
    Set rs = Nothing
End Function

Amend the red bits.

To call:

GetAbb([Field4])

it is stopping the query and returns as
Runtime error 5
Invalied Procedure or Argument

Hari

 

[vbaInet]

You need to put the Function in a Standard Module. Not a form module.

 

[welcomesui@gmail.com]

You need to put the Function in a Standard Module. Not a form module.

it is in Standard Module Only, Module4 is used this function

 

[vbaInet]

Ok, do you know where the Immediate Window is?

Also, I replaced a line in the function (but it's not related to your problem but needs to be replaced). Here it is:

    [COLOR=Red]GetAbb [/COLOR]= Left(strAbbs, Len(strAbbs) - 1)

It was the red bit I changed.

 

[welcomesui@gmail.com]

I changed the code as suggested

same problems repeats

Find sample attached

 

[vbaInet]

I see what you mean.

See attached.

 

[welcomesui@gmail.com]

It is giving the only abbvaitons
not total data
It should return along with the data in Field4