Calculated filed with if-then

Question

I am working on creating some calculated fields

The data for the "test2" field is imported from Access consists of numbers or "Null" in text.

I also have fields "test1" and "minimum" and "maximum" where all the three parameters are numbers.

I would like to create a calculated field with an if statement.

I would like to do the following:

1. If "test2" is not "Null" and "test2" < "minimum" then calculate "minimum" - "test2". (I was using the syntax IIF and != for not equal, but it did not like it because "Null" is a string value)
2. else if "test2" is not "Null" and "test1" < "minimum" then calculate "minimum" - "test1"

How should I create this calculated field? Please advise.

Answer 1

Below is the required code: 

IF (NOT ISNULL([test2])) AND [test2] < [minimum]
THEN [minimum] - [test2]
ELSEIF (NOT ISNULL([test2])) AND [test1] < [minimum]
THEN [minimum] - [test1]
END

I guess ISNULL is the part where you got stuck. You can check out this link to learn more on ISNULL.

Comments

@Grim Thankyou for answering my query and giving additional useful resources for learning. This set of code worked for me.