Replacing query output based on multiple strings (Access 2013)

[suprdave]

Hello All,

I am looking for a simple solution to replace output from my query.

Here's the scenario:

Table name: Sometable
Field name: Sometext

Existing query output with no replacement statement:

Record 1: This output contains red
Record 2: This output contains blue
Record 3: This output contains green

My Criteria: (I realize this is not correct syntax, but wanted to clearly define what I'm trying to achieve)

If output contains "red", replace entire string with "10"
If output contains "blue", replace entire string with "20"
If output contains "green", replace entire string with "30"

Expected output:

Record 1: 10


Record 2: 20
Record 3: 30

Thanks,

Dave

[cyanidem]

NewOutputField: IIf(InStr(1,[Sometext],"red"),"10",IIf(InStr(1,[Sometext],"blue"),"20",IIf(InStr(1,[Sometext],"green"),"30")))

[June7]

Or:

NewOutputField: Switch(InStr([Sometext],"red")>0,"10", InStr([Sometext],"blue")>0,"20", InStr([Sometext],"green")>0,"30")

[suprdave]

cyanide,
Worked perfectly!
Thanks!