replace in update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
..
 
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...
 
Back
Top