replace comma with period automatically in field 1

[JCAA]

I have a lot of products and a client in Germany insist of sending them with comma instead of period in the name eg part# 321,2 instead of 321.2. Can I write a query to update all the commas to periods in my field in a table? I know that it is easy to do a search and replace but I prefer to just have a query to do it so I do not forget to do it any week.

Thanks!

 

[cghoga]

Yes, try the following code:

DoCmd.SetWarnings False 'Turn messages off.

DoCmd.RunSQL "UPDATE YourTableName SET YourTableName.NameofPartNumField = IIf(InStr(1,[NameofPartNumField],',')=True,Left$([NameofPartNumField],InStr(1,[NameofPartNumField],',')-1) & '.' & Right$([NameofPartNumField],Len([NameofPartNumField])-InStr(1,[NameofPartNumField],',')),[NameofPartNumField])"

DoCmd.SetWarnings True

Note: Replace where the above says NameofPartNumField and YourTableName as applicable to your database. Also, you might try this on a temp table until you're sure it's doing what is needed.

Hope this helps you.

 

[cmmrfrds]

Update yourtable
Set yourfield = Replace(yourfield, ",", ".&quot

 

[JCAA]

What would my best solution be if I also have unwanted " in my file and want to replace them with nothing? Cmmrfrds comment will not work for this character correct?

Thanks,