Update Query question 2

[Snappy2873]

Hello everyone,

I'm trying to create an update query that doesnt erase all my data and leave just a ",". My goal is to create/run a query on a keyword column(text(255)) that has data with no space after the comma and then add the space. Check out an example of my data:

wonderware,validation,software,htv,oven,intouch,computer,protocol,#1through#6,htv,temperature,vulcanization,dipping,oven-htv-01,oven-htv-02,oven-htv

I've tried and update query on this column that basically says: UPdate column keyword to ", "(,space) where data is Like "*,*" but the query left me with a column that has just a comma with no data. I also tried the Like ",", but it didnt return any records that could be changed.

Any help would be greatly appreciated.
Thanks
snappy

 

[traingamer]

Have you tried

 UPDATE column keyword to (keyword & " ")
where data is Like "*,*"

or about 10 other ways.

You need to use the existing field, not just replace it.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lespaul]

depending on your version of access there is a Replace function, I think the syntax is:

SELECT FieldName, Replace(FieldName, ",", " ") As New Field FROM TableName

verify it works with the select and then you can change it to:

UPDATE TableName Set FieldName = Replace(FieldName, ",", " ")


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[traingamer]

And your Like statement should be

like "*,"

to get only the field(s) that ends in a comma.
or

where Right(keyword, 1) = ','

etc. etc

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

You may try this:
UPDATE yourTable
SET keyword = Replace([keyword], ',', ', ')
WHERE keyword Like '*,*' AND keyword Not Like '*, *';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Snappy2873]

Hey everyone,
the replace function worked like a charm. thanks to everyone for the help. here's the final syntax:

UPDATE test SET dockeyword = Replace([dockeyword], ",", ", ");

Thanks again,
snappy

 

[traingamer]

And, of course, I misread the specs.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill