Replace Function help

  • Thread starter Thread starter koturtle
  • Start date Start date
K

koturtle

I have a query with a field (WBS) that i need to replace part of the
field under a certain circumstance.

I need the wbs to change from 001.01.01 to 01.01 if it starts with A
but not if it starts with B.

so change A001.01.01 to A01.01.

TIA

KO
 
I'm curious how the field WBS can be 001.01.01 and start with an A or B.
Assuming that that the actual field vaue is A001.01.01 or B001.01.01, then
something like this should work:

Update TableName
Set WBS = SUBSTR(WBS, 6, 5)
Where WBS like "A%"

Obviously you need to replace TableName with the name of your table. And
you should probably experiment with something like this to make sure you are
replacing just what you want to replace.

SELECT WBS, SUBSTR(WBS, 6, 5) AS NewVal
FROM TableName
Where WBS like "A%"

You may need to change the start and/or length parameters to the SUBSTR
function depending on what your data actually looks like.

Ray
 
I have a query with a field (WBS) that i need to replace part of the
field under a certain circumstance.

I need the wbs to change from 001.01.01 to 01.01 if it starts with A
but not if it starts with B.

so change A001.01.01 to A01.01.

TIA

KO
Click to expand...

NewColumn:Replace([WBS],"A001","A01")
 
You can use an expression like this to return "A" plus all the
characters after the first period in WBS.

IIF(WBS like "A*","A" & Mid(WBS,Instr(1,WBS,".")+1) ,WBS)

If that is not what you want I suggest you post two or three samples of
what you want to change and what they should change to. And if you can
state the rules for changing the value then do so. You made a start with
-- Change if it start with A and not with B

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a query with a field (WBS) that i need to replace part of the
field under a certain circumstance.

I need the wbs to change from 001.01.01 to 01.01 if it starts with A
but not if it starts with B.

so change A001.01.01 to A01.01.

TIA

KO
Click to expand...

NewColumn:Replace([WBS],"A001","A01")
Click to expand...
 
Back
Top