asked on

string comparision in access

hi ,
I need to update all rows in a table only for a certain field.
however i only need to update the value starting from position 5 to 3 byes.
like
update table_a
set field_a(5,3) = "555"

but i do not know the proper syntax.
thanks

perkc

Use the 'Replace' function:

UPDATE table_a
SET field_a = replace([table_a]![field_a],5,3,"555");

perkc

chakrika

ASKER

Hi i tried to do that. but i get the following error message:
Microsoft Access didn't update 0 field(s) due to a type conversion failure, 0 records(s) due to key violations, 0 records(s) due to lock violations, and 32 record(s) due to validation rule violations Do you want to continue running this type of action query anyway? To ignore the error(s) and run the query, click yes. For an explanation of the causes of the violations, click Help.

chakrika

ASKER

I changed teh query to do this.
UPDATE table_a
SET field_a = replace(5,3,"555");

but then it replaced the entire value in the table with onlyjust '5'.

thanks

chakrika

ASKER

ok i understand that the replace function should be used like this
REPLACE(old_text,start_num,num_chars,new_text)

But still i get the error message i mentioned above. I cannot update.

perkc

Give me a couple of examples of the type of values that you have in the 'field_a' field.

Also, what format is the field_a table field? Text? Number?

ASKER CERTIFIED SOLUTION

kalios

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chakrika

ASKER

super kalios.

works like a charm
thanks