Update Query I think.....?

[spnz]

Good afternoon


Hoping someone can help me out.

I have a field that contains a persons full name and at the end of their name in brackets either (S) or (NS).

I have created a new field that will contains the numbers 0 for S & 1 for NS.

How would I run a query that..

A. checks to see what the letter in the bracket is and then puts the correct number into the new field.
B. Automatically deletes the (S) or (NS) after adding it to the field



If anyone can help out I would be most grateful

 

[mresann]

Assuming table name tblTable, name field FullName, and new field called Status,

UPDATE [tblTable] SET [tblTable].FullName= (Trim(Left([tblTable].[FullName],InStr([tblTable].[FullName],"(")-1))), [tblTable].Status = IIf(InStr([tblTable].[FullName],"(NS")>0,1,0);