Multiple if condition

Hi, I require help with Below:

I have 3 columns named as "Prep 1", "Prep 2", "Prep 3" I need to create new column as "prep4" having below conditions:

If "Prep2" is Null then take value from "Prep3"

If "Prep3" is Null then take value from "Prep2"

If "Prep2" and "Prep3" both are NOT Null then take value from "Prep2"

If "Prep2" and "Prep3" both are Null then take value from "Prep1"

Accepted answers

This should do the job,

if isnull([Prep2]) and isnull([Prep3]) then [Prep1]
elseif isnull([Prep3]) then [Prep2]
elseif isnull([Prep2]) then [Prep3]
else [Prep2] endif

This should do the job,

if isnull([Prep2]) and isnull([Prep3]) then [Prep1]
elseif isnull([Prep3]) then [Prep2]
elseif isnull([Prep2]) then [Prep3]
else [Prep2] endif

Actually, if you don't have any zeros, I think you get the same thing with the simplified,

if [Prep2] then [Prep2]
elseif [Prep3] then [Prep3]
else [Prep1] endif

So I have tried but in case if both Prep 2 and Prep 3 are having value it is taking value from Prep2 however I need it from Prep3

Not according to your original post. However you can switch the second and third clause in that case,

if isnull([Prep2]) and isnull([Prep3]) then [Prep1]
elseif isnull([Prep2]) then [Prep3]
elseif isnull([Prep3]) then [Prep2]
else [Prep2] endif