If Than Else Statements: how to compare fields? 2

[chuck74]

Hello,<br>
<br>
I am trying to write an if then else statement in the query design view. My first field is named result. It is usually a small number with up to 5 decimal places. The second field is called Action Limit. It is also a numerical field with decimal places. The new field, the one containing the expression, is Column J. I want column J to contain text that states whether the result is &quot;greater than the action limit&quot;, &quot;less than the sction limit&quot; or, if Result = 0, &quot;not detected&quot; I want to write an expression that says:<br>
<br>
If Result = 0 then Column J is &quot;Not Detected&quot;<br>
<br>
ElseIf Result &gt; Action Limit then Column J is &quot;Greater than Action Limit&quot;<br>
<br>
Else Column J is &quot;Less than Action Limit&quot;<br>
<br>
The final outcome would be a table like this, except many more rows:<br>
<br>
Result Action Limit Column J<br>
7 1.2 Greater than Action Limit<br>
.05 1 Less than Action Limit<br>
0 3 Not Detected<br>
<br>
I haven't written statements for years. I'm not sure about the correct syntax for entering a field. Any help would be excellent.<br>
<br>
Thankyou,<br>
Chuck<br>
<br>

 

[elizabeth]

You can't put the If stmt used in VB code into the Access Query Design Window. But you can use the Iif (immediate IF) function instead. Open your (...) build window on the condition line of your query and you will be able to choose Functions and then find Iif. It works like this: If condition = true, then result1, else result2. The else is optional, and you can nest the iifs. So you want to do something like this:<br>
<br>
IIf (result&lt;&gt;0,(if(result&gt;actionlimit, &quot;greater than...&quot;,&quot;less than ...&quot,not detected)