replace in update query

U

uarctos

I have a field in which some of the records end with text enclosed in parens:
parameter value remark PCODE_ID units
Air Temp (deg C) 9.16 20 degrees C

I want to delete the space and all the text enclosed in the parens.
Here's what I've been trying:

UPDATE ResultsIBWC_ws SET ResultsIBWC_ws.parameter = Replace([parameter],"
(*)","");

Please help me see what I'm doing wrong.
Thanx
 
J

John Spencer

Replace does not work with wild cards. So you are going to have to use
Instr and Left and Mid to do this.

UPDATE ResultsIBWC_ws
SET ResultsIBWC_ws.parameter =
Left([Parameter], Instr(1,[Parameter],"(") -1) & Mid([Parameter],
Instr(1,[Parameter],") ") +1)
WHERE ResultsIBWC_ws.parameter LIKE "*(*)*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
U

uarctos

Thanks - very similar to something I finally got to work:
UPDATE ResultsIBWC_ws SET ResultsIBWC_ws.parameter =
Left([parameter],InStr([parameter],"(")-1)
WHERE ((CStr(InStr([parameter],"(")) Not Like "0"));

John Spencer said:
Replace does not work with wild cards. So you are going to have to use
Instr and Left and Mid to do this.

UPDATE ResultsIBWC_ws
SET ResultsIBWC_ws.parameter =
Left([Parameter], Instr(1,[Parameter],"(") -1) & Mid([Parameter],
Instr(1,[Parameter],") ") +1)
WHERE ResultsIBWC_ws.parameter LIKE "*(*)*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

uarctos said:
I have a field in which some of the records end with text enclosed in
parens:
parameter value remark PCODE_ID units
Air Temp (deg C) 9.16 20 degrees C

I want to delete the space and all the text enclosed in the parens.
Here's what I've been trying:

UPDATE ResultsIBWC_ws SET ResultsIBWC_ws.parameter = Replace([parameter],"
(*)","");

Please help me see what I'm doing wrong.
Thanx
Click to expand...
Click to expand...
 
Top