We help IT Professionals succeed at work.

Access SQL, Remove not needed text values.

ggodwin
ggodwin asked
on
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)
Comment
Watch Question

Top Expert 2016
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],"-")));
Top Expert 2014

Commented:
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

ggodwinQuality Engineer

Author

Commented:
This worked perfectly.