Replacing all commas in a long text field with semicolon.

[JRodko]

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

[June7]

Do you mean it returns all records? Yes, that would be expected without filter criteria. But you can enter filter crtieria in UPDATE design grid. Works for me.

Query builder always opens to SELECT. I just clicked on UPDATE icon and then entered filter criteria.

[JRodko]

Now that you've said it it makes total sense. It was returning all records because I had no filter criteria but the replace function would have still worked.

Is there any way to make this run across several tables in one query or do I have to build a query for each one?

[June7]

Query would have to be editable dataset, which would probably not be the case.

[Micron]

How many tables? How many fields? If a query you create with several/all related tables isn't updatable you're looking at doing whichever ones you can join and still update or a one by one approach or a code solution. In code you could do an inner loop through all fields and use Replace function, within a loop that iterates over all tables. However, you'd need a lot of tables/fields to make that worth while - unless you're lucky enough to find code that someone has already written for this.

[JRodko]

Thanks Micron. I don't have that many tables, maybe a dozen or so tops that I have to check. Won't take me long to implement the queries. Just would have been nice to do it all at once!