I swear I just had this working, but I accidentally deleted the query and now I can't get it to work again!
I am trying to use the Replace function in an update query to replace all "," in a field with ";". This is because it obviously causes problems when I export to csv and import into another program.
It is only trying to run on one field in one table currently. Here is the SQL statement.
"UPDATE Lithology SET Lithology.Lith_desc = Replace([Lith_desc],",",";");"
but this returns the fields for all records in that table, even those that do not have a comma in them ",".
When I do a select query and make the Criteria Like "*,*" and the move to an update query with the same function as above, everything seems to work. The final SQL statement is
UPDATE Lithology SET Lithology.Lith_desc = Replace([Lith_desc],",",";")
WHERE (((Lithology.Lith_desc) Like "*,*"));
Can someone explain to me why this only works when I start with a select query? Is there any possibility of unexpected results? I am nervous about it because I don't understand it completely and don't want to see entire fields be replaced with a ";".
Will this work reliably if I try to do the same find and replace for fields from multiple different tables in one query? They typically share a relationship though it is not within the fields that I will be updating.
Thanks