Access SQL, Remove not needed text values.

Table: DSCNSKPIUPDATE2

I have text in this field that is not required. The format of the data varies. However, all i need is the "Closed" or "Open".
How can I remove all other characters?

6 - Closed (DNKY)
1 - Open (On Schedule)
4 - Closed (Non-DNKY - Not On Record)
6 - Closed (DNKY)
ggodwinAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this update query, change tableName and FieldName accordingly

Note: create backup copy of your table before running the query

UPDATE TableName SET TableName.IssueStatus = Trim(Mid([FieldName],InStr([FieldName],"-")+1,InStr([FieldName],"(")-1-InStr([FieldName],"-")));
0
 
aikimarkCommented:
A slightly different parse, based on your data example.  Replace "FieldName" with the name of the field.
UPDATE DSCNSKPIUPDATE2
SET DSCNSKPIUPDATE2.FieldName = Mid(DSCNSKPIUPDATE2.FieldName, 5, InStr(5, DSCNSKPIUPDATE2.FieldName, " ")-5)

Open in new window

0
 
ggodwinAuthor Commented:
This worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.