update field value

[stalk]

I have one field called results with values 'Res Positive', 'Res Negative' and 'Invalid'. When I run this query to update another table I want to just take 'Positive', 'Negative'and 'Invalid'. that mean I need to remove 'Res' if the result is Positive or Negative and read any other value as is.


It should be straight forward but not working for me.


Thank you

[Gicu]

Use Mid([Result],Instr([Result]," ")).

Cheers,

[stalk]

This mid function is only working for 'Res Positive' and 'Res Negative' values. If the result field has Invalid or repeat this function is give me #Func!.
How to resolve this?

[ssanfu]

PMFJI,

Using Vlad's formula, I changed it to

IIF(Instr([Result]," "),Mid([Result],Instr([Result]," ")+1),[Result])

I added the +1 to remove the space after "Res"

[Gicu]

Hi Steve,
Shouldn't it be IIF(Instr([Result]," ")>0,Mid([Result],Instr([Result]," ")+1),[Result])?
Cheers,

[ssanfu]

I would work both ways. The syntax of the IIF() function is IIf(expr, truepart, falsepart)

The expr MUST evaluate to TRUE or FALSE.
FALSE equals Zero and TRUE is Not False. So any numeric value that is Not False (ie not 0) is True.

0 = False
1 = True
100 = True

If you search for a space in the field "Result" (the expr) and there is not a space, then a Zero is returned, so use the FALSE part, which is [Result].
If a space IS found, in this case it is the 4th character, so it result is True (Not False). Therefore use the TRUE part expression -> Mid([Result],Instr([Result]," ")+1).

[stalk]

Thank you Vlad and Steve. Works both ways with or without >0.
You guys are awesome