Quick question.

If I want to find the character ; each time it shows up in a field and replace it with , . What would I do?

Table name is "ONE BIG TABLE", field name is "LG_DESC".

I guess I'm looking for code I can use in SQL View for an Update Query!

TY

I tried this


UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = ","
WHERE ((([ONE BIG TABLE].KEYWRDS)=";"));


but it gives me back the ole "Will update 0 records". I need to replace the ; if it's within the data, so maybe it's looking for an exact match and because it's within the data it's not finding it?

that will not do what you think
if that ran it would set the value of [ONE BIG TABLE].KEYWRDS to "," if [ONE BIG TABLE].KEYWRDS was ';'
so it would work if the column keywrds only contained the symbol ";"

you could use the wild card symbols to tell the SQL engine to look for ";" anywhere in the column keywrds, but your next problem is that you are trying to set the new value as ","
ie
if the current value of Keywords was "... and we use a semi colon; here; and here again; balh di blah"
your current code would set Keywrds to ",".

you'd be better off using the repalce function.. there is a reason why I keep suggesting you read up on the Access string functions

so you'd be better off
using something like
update mytable
set mycolumn = replace(mycolumn, ";", ",")
where mycolumn like "*;*";[code]

as ever before running an update query take a backup so if the update or delete goes wrong you haven't affected your data
you can test the where clause by usign a query such as
[code]select * from mytable
where mycolumn like "*;*";[code]

however Im confused:-
you intially refer to LG_DESC as the column name with the problem, then refer to a different column KEYWRDS

"Access string functions"

Believe me, I'm reading. My challenge is that everywhere I go, there's a seemingly different spin on string functions. I guess I need a book or a good tutorial somewhere. I'm likely to read the web or tutorial.

"however Im confused:- you intially refer to LG_DESC as the column name with the problem, then refer to a different column KEYWRDS"

You are rightly confused. I was looking at my 5 remaining tasks and wrote the wrong thing / confused it with another post.

To bring closure to this thread. Your instructions once again worked!

This is what worked. And yes I did backup my data Now I have a question though. See below.


UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,";",",")
WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*;*"));

Related / New Question(S)
If I want to take this same data set and say, replace X with Y would this be correct? This is to save for future use.

UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"X"Y"Y")
WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*X*"));

If I wanted to just strip the character ™ and replace it with nothing, would this be correct?
UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"™""")
WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*™*"));

right idea, wrong implementation

to be honest I don't know what would happen using replace on ™. I don't know if ™ is stored as a string or special character. but looking at Google it seems Access uses Chr$(174) for the tm mark
Trademark Symbol - Microsoft Access / VBA

if that fails then try
the chr function returns the symbol represented by the number form the current ascii character table. there is a corresponding function which returns the ascii value of the symbol ASC(mysymbol)

well for a start there's google
Google

which has a very promising reference (N0:2 on the list returned to me) of:-
String Functions Listed By Name

Thank you

On the ™ replace. this did work.

UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].KEYWRDS = Replace(KEYWRDS,"™","")
WHERE ((([ONE BIG TABLE].KEYWRDS) Like "*™*"));

The generic find and replace was just to save as a base for future ones.

"which has a very promising reference (N0:2 on the list returned to me) of:-
String Functions Listed By Name"

That's good info! I'm checking it out now! Thank you very much!