update query

[lestats]

Hello,

I have a field with given names such as :

Mrs Gordon
Mrs Doyer
King
Larry

I would like to remove only the Mrs title in the field in order to have only the given names without the title Mrs :

Gordon
Doyer
King
Larry

So far I tried a update query :

UPDATE [Copy Of matched uptake_OASP1208] SET [Copy Of matched uptake_OASP1208].GIVEN_NAME = Left([Copy Of matched uptake_OASP1208]![GIVEN_NAME],3)=" "
WHERE ((Left([Copy Of matched uptake_OASP1208]![GIVEN_NAME],3)="mrs "));

It doesn't work. It returned 0. It seems my rational is not good in my SQL.

Any helps would be welcome.

 

[AccessMSSQL]

Use the Replace function in your update criteria:

Update tablea
set PersonName = Replace([PersonName],"Mrs","")

 

[AccessMSSQL]

You might also want to use Ltrim to trim any spaces: Ltrim(Replace([PersonName],"Mrs",""))

 

[lestats]

Thx AccessMssql. It works perfectly. I used :

SET [Copy Of matched uptake_OASP1208].GIVEN_NAME = Replace([Copy Of matched uptake_OASP1208]![GIVEN_NAME],"mrs"," ")
WHERE (((Left([Copy Of matched uptake_OASP1208]![GIVEN_NAME],3))="mrs"));. I will try the Ltrim function to remove spaces.