SQL update query not working

[Christine]

Hi group,

I'm puzzled by this problem.

I need to replace commas in a document with "comma + space". I can't do this
in Excel because I get an error that says "Formula too long". I could do this
in Word, but I risk replacing commas not in the column I want to replace.

I imported my spreadsheet into Access and made this query:

UPDATE tblImportData
SET metadata = ", "
WHERE metadata = ","

But it doesn't work. There is no indication that it even starts to run.

I thought, well, OK, this isn't working, so I tried just a normal find and
replace on my metadata column. The find and replace only worked about half
the time. How can I get around this?

Thanks,
Christine

 

[Norman Yuan]

So, in the table (tblImportData), you have a column called "metedata", and
the value of that column of some records are ","? Your SQL UPDATE code only
update the records that have a single character value "," to ", ". However,
I suspect what you want is that while the metedata column has a chunck of
characters (or a paragraph of text), you want to replace "," among the
characters.

So, the SQL statement should be

UPDATE tblImportData
SET metadata=REPLACE(metadata,',',', ')

 

[Christine]

Norman, thank you so much. That was exactly what I was looking for - plus I
learned something new!

Best,
Christine