add a specified character to every record in a colume?

[andrei186]

How shal I add a specified character to every record in a colume?


I tried

UPDATE table_name
SET field_name ='character' +field_name

But on the first run it returned the correct colume but with no character added

When closed and attemted to run again? it would not open saying "this action has been blocked in the switch-off mode" (I tranlate it from Russian into English so the actual wording in English might be different)

[TheShabz]

I dont think you can update a field with itself. Try creating a brand new field and updating that one. So:
UPDATE TableName
SET NewField = "character" & OldField

Then delete the old field and rename the new one to what the old one was.

The & and + shouldn't matter in your case because it is a string, but I'd recommend getting away from + and using & as if you try to concatenate to numbers with + you're going to add them together instead.

[alansidman]

Try this:

UPDATE table_name
SET table_name.field_name ="character" & [field_name];

This worked for me in a sample db.

Alan

[TheShabz]

GUess you can update a field with itself. Thanks, Alan.

[andrei186]

Thank you, both.
On both versions of the query I have the same result as with my first code - the query would not run but throws a warning "this action has been blocked in the switch-off mode" - what might this mean? It looks like the problem is not the query syntax but some setting of my database? This is Access 2007 on XP

[alansidman]

Andrei;

Difficult to say without seeing the db. Suggest you post a copy with limited data included. Run a compact and repair before uploading and also suggest you remove any confidential data. Post as an earlier version as many users on this forum are still running 2003/2002 versions of Access and you want maximum viewers to look at this.

One last thought: is the field you are trying to update a text field and are you trying to add text to it? I wonder if there is a data mismatch that is causing this.

Alan

[andrei186]

Thanks, Alan.
I've just created a small test DB from the scratch and your query worked in it.
So the problem is indeed in DB. It is quite a big one (four tables of about 1000 records each). Before bothering you with it I'll try to play with it myself, and if fail, will use your invitation.
One thing which might be relevant:
this DB has actually been created time ago when this computer was running w2k and the DB was created with ACCESS-2000. Now I run it on XP/Access2007 ( (BTW I think I should drop XP and Office-2007 because of so many small annoying things, and go back to w2k-Office2000)) - perhaps this might have to do with that warning message?
PS I forgot to tell that the field is a text one, and its length is suffficent to accomodate another character