Find & replace in VBA

[merry_fay]

Hiya,

Unfortunately I can't seem to find examples of how the find & replace works in VBA so please can someone help me finish my bit of code!

Situation:
A number of tables are imported with the My_Field column having items such TSM_usualtext_blah

Blah could be anything.
All the other tables it links to only have the "usualtext" bit so I want to get rid of all the TSM_ bits, then _* for the dodgy endings.

Code I'm trying to get work to build up to this:

autoFindNReplace([My_Table], [My_Field], " TSM_ ", Null, DO_ALL_RECORDS).Run

The VBA editor was expecting something after it to finish the line of code so I added the .Run -I'm not sure if this is correct as I can't find any examples. When I run it, I don't get any error messages, but also if I stick a message box after it, it doesn't pop up. Any suggestions as to how to finish it?
I need it to look at any part of the field rather than the whole field contents too.

Thanks

 

[pr2-eugin]

Could you give us the code for the Sub/Function autoFindNReplace ??

 

[merry_fay]

Ahhh, that is probably where my problem lies then. I was thinking it was an inbuilt VBA function..... (all google's fault )

Re-hash to my question then, how do I do a find & replace in VBA on a table?

Thanks

 

[pr2-eugin]

Before going there, we would need to know how the table is structured, some sample date, what is the outcome you wish to see.. It might be a bit trickier without that information..

 

[merry_fay]

This is a sample of the data:

My_FieldNumberANumberBTSM_XYZ_ORT243265916275.77875TSM_FAB_ORT225259030.814680.243TSM_XTDC_DBA4161551.971286.26974TSM_XXX_DB2417607.0378.28114488TSM_XYZ80881.108246.4329725

I want the My_Field (not a primary key) field to end up with
XYZ
FAB
XTDC
XXX
XYZ

It needs to be done via VBA.

Thanks

 

[pr2-eugin]

I am sorry could you please edit the data to a more presentable/readable format?? Use CODE or QUOTE tags to surround them to preserve the format, you can preview post before you actually post it..

[B]fieldName    anotherField     maybeAnother     sureAnother[/B]
Name Here    Address 1        009789454        08/10/1989
New Name     Address 2        005478465        02/05/1983
Name Here    Address 1        009789454        08/10/1989
New Name     Address 2        005478465        02/05/1983

 

[bob fitz]

merry fay

Perhaps you could use an Update query. see: http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx

 

[merry_fay]

Apologies, I'm not very good with posting...

[FONT=Calibri]My_Field                     [/FONT][FONT=Calibri]NumberA            [/FONT][FONT=Calibri]NumberB[/FONT]
[FONT=Calibri]TSM_XYZ_ORT             [/FONT][FONT=Calibri]24326591          [/FONT][FONT=Calibri]6275.77875     [/FONT]
[FONT=Calibri]TSM_FAB_ORT2          [/FONT][FONT=Calibri]25259030.8[/FONT][FONT=Calibri]1     4680.243[/FONT]
[FONT=Calibri]TSM_XTDC_DBA          [/FONT][FONT=Calibri]4161551.97       [/FONT][FONT=Calibri]1286.26974[/FONT]
[FONT=Calibri]TSM_XXX_DB2            [/FONT][FONT=Calibri]417607.03          7[/FONT][FONT=Calibri]8.28114488[/FONT]
[FONT=Calibri]TSM_XYZ                       [/FONT][FONT=Calibri]80881.10            82[/FONT][FONT=Calibri]46.4329725[/FONT]

Wow that took a lot of formatting!!

As for an update query, I can't see how I would make it work. The My_Field column will be the link to other tables only AFTER it's had the TSM_ & the _* removed, until then it only exists in this table so I can't use another table to update it.
Can you write an update query for "every record starting TSM_, update to the rest of that record without the TSM_"?

Thanks

 

[bob fitz]

Can you write an update query for "every record starting TSM_, update to the rest of that record without the TSM_"?

Yes, I believe you can.

 

[bob fitz]

Yes, I believe you can.

Backup your data first!
The SQL for it would be something like:

UPDATE [B][COLOR=red]tblYourTable[/COLOR][/B] SET [B][COLOR=red]tblYourTable[/COLOR][/B].My_Field = Mid([My_Field],5)
WHERE ((Left([My_Field],4)="TSM_"));

Substitue tblYourTable with the name of your table.