Changing only selected text. 1

[bosk00]

I am not sure if I should be thanking you all or cursing you. With your help I imported data from our AS400, originally it was suppose to be a one time deal, It came in all Caps and got converted with "StrConv" to Upper/Lower. After running a query, we were able to correct any oddities that we had, an example Abc Manufacturing, Llc, needed to be corrected to ABC Manufacturing, LLC. Sometimes things work to well. There will now be a monthly import of data. Over time I will find all of the things that need to be corrected. Is it possible to make an update query that will look for specific things in a field and change just that part. In the example above to look for Llc and change it to LLC.

Thanks

Alan
Senility at its finest

 

[PaulBricker]

An update query will not act like a Find and Replace feature. If you want to update ABC Company, Llc with LLC, an update query will change the entire value "ABC Company, Llc" to "LLC"

To do what you want, you could create a form with a textbox that held the value you wanted to update, in this case Llc, and the value to update with, LLC, and then use the Recordset method and the InStr() Function to open the recordset and locate every occurance of the first string and replace it with the second string.

Post back if you want some code that might get you going.

Paul

 

[bosk00]

Paul, That would be great.

Thanks

Alan
Senility at its finest

 

[PaulBricker]

I found the Replace function will do what you need. The code would look something like this.

Function ChangeCase()
Dim rst As DAO.Recordset
Dim mystr As String,myOld as String, myNew as String
myOld = Forms!Form1!Text1
myNew = Forms!Form1!Text3
Set rst = CurrentDb.OpenRecordset("tblTableName", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF

mystr = Replace(rst!myInfo, myOld, myNew)
rst.Edit
rst!myInfo = mystr
rst.Update
rst.MoveNext
Loop

End Function

You could call it from a button on the form.
Please test it on sample data and post back with specific problems.

Paul

 

[bosk00]

I put the code into a module and created the form and text boxes. I added a button and that is where I am lost on what to put in the on_click event of the button to run the function.

Alan
Senility at its finest

 

[PaulBricker]

In the On_Click event you would put

Call ChangeCase()

That should do it.

Paul

 

[bosk00]

When I run the function I get an error that says "Item not found in this collection"

If I go to Debug, the mystr = Replace(rst!myInfo, myOld, myNew) is highlighted

I think the problem is with the rst!myInfo, not sure.



Alan
Senility at its finest

 

[PaulBricker]

rst!myInfo is the name of the field you want to be searching in in your recordset. You need to change the name of that field to whatever field you want to search. So if the name of your field is "Company" then you would replace rst!myInfo with rst!Company

Give that a try and see what happens.

Paul

 

[bosk00]

Paul, Thank you so much it works great and here is a much deserved star. That the same things will need to be updated every month, would the same concept be able to get the myOld, and myNew pairs from a table?

Alan
Senility at its finest

 

[PaulBricker]

It will depend on what you are trying to get from the table. As an example, if you have a table that has two fields in it, fldmyOld and fldmyNew and it had 1 record in the table
fldmyOld fldmyNew
Rec1 Llc LLC

then there would be no issue getting the values from the table and passing them to the Function. If the table has multiple records,

fldmyOld fldmyNew
Rec1 Llc LLC
Rec2 inc Inc
Rec3 Lld LLD

then you have to know which record your Function needs to reference to get the values it wants. This is a little more complex. If you can give me some info on how your system will work, I can probably suggest a method to handle it.

Paul

 

[bosk00]

Paul, The access database is automating some tasks that were being done manually with data from the AS400. Using your 3 record example, this would be the ChangeCase Table. Ideally it would use the first pair fromthe ChangeCase Table and make the change to all records in the main table, then it would loop and pick the second pair and make the changes, and continue until it has used all of the pairs in the ChangeCase Table. This way as new pairs are found they could be added.


Alan
Senility at its finest

 

[PaulBricker]

Then it will just take a little changing of the original code. The code can be condensed a little, but I left it this way so you could see what was happeing easier. The parts in BOLD are names that you will have to change to the names of your tables and fields.

Function ChangeCase()
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Dim db as DAO.Database
Dim mystr As String,myOld as String, myNew as String
Set db = CurrentDb
Set rst2 = db.OpenRecordset([b]"tblChangeCase"[/b],dbOpenDynaset)
   rst2.MoveFirst
     Do Until rst2.EOF 
      myOld = rst2![b]fldOld[/b]
      myNew = rst2![b]fldNew[/b]
Set rst = db.OpenRecordset([b]"tblTableName"[/b], dbOpenDynaset)
    rst.MoveFirst
      Do Until rst.EOF
       mystr = Replace(rst![b]myInfo[/b], myOld, myNew)
          rst.Edit
          rst!myInfo = mystr
          rst.Update
     rst.MoveNext
     Loop
    rst2.MoveNext
    Loop

Set rst = Nothing
Set rst2 = Nothing
Set db = Nothing
End Function

Please test it on sample data and let us know how it goes.

Good luck.

Paul

 

[bosk00]

Paul, Thank you again, it works like a charm. I really appreciate your help. A muchly deserved star for the looping function, I wish I could bestow more.

Alan
Senility at its finest