Explain iif in ms access

[pabbiraj]

Actually, what i want to know, I have two text boxes, i.e.text1, text2. text1 contains numeric data i.e., 12,215,300,0.56....and text2 contained i.e ">=200","<=200","<10%".... I want to use the formula like this..if the text1 data is fallen in text2, then show as "Pass" else "Fail"..Plz see the data like this..

Text1 Text2 Result
12 >=200 Fail
215 <=200 Fail


150 >=100 Pass

just like the data should come and the result column should show the result.

[NTC]

you cannot have the text symbols > < = etc in the number field 2. those go in the calculated iif field

Text1 Text2 Result
12 200 Result: iif([Text1] >=[Text2],"Fail","Pass"
215 200
150 100

Your calculated field has the same algorithm applied to all rows. If you need to vary the algorithm then you need to potentially have a second calculated field - - as well as a third calculated field that contains the logic of whether to use the first or second calculated field.

One can nest the iif statements within each other - but at a point this is difficult to manage and troubleshoot and so generally it is somewhat easier to put calculated iif fields side by side....

hope this helps

[ssanfu]

There is a work around using the Eval() function.

On a form there are 3 bound controls on a form: the control that has just the number is named Number1, the control with the operator(s) and number is named Text1 and the 3rd control is named Result1.
The main problem is getting the operator(s) separate from the number that is in Text1.


In the after update event of Number1 is this code:

Private Sub Number1_AfterUpdate()
   If IsNull(Me.Number1) Or IsNull(Me.Text1) Then
      Me.Result1 = "??"   ' my way of showing a missing number. Could be "Fail"
   Else
      Me.Result1 = PassFail(Me.Number1, Me.Text1)
   End If
   'commit (save) the record
   Me.Dirty = False

In the after update event of Text1 there is this code:

Private Sub Text1_AfterUpdate()
   If IsNull(Me.Number1) Or IsNull(Me.Text1) Then
      Me.Result1 = "??"   ' my way of showing a missing number. Could be "Fail"
   Else
      Me.Result1 = PassFail(Me.Number1, Me.Text1)
   End If
   'commit (save) the record
   Me.Dirty = False
End Sub

This is the code for the function PassFail():

Public Function PassFail(pN1 As Single, pT1 As String) As String
   Dim N2 As Single
   Dim OP As String
   Dim tmp As String
   Dim i As Integer
   Dim v As Variant
   Dim b1 As Boolean

   'set default value
   PassFail = "Fail"

   'get the number and convert to decimal
   For i = 1 To Len(pT1)
      v = Mid(pT1, i, 1)
      Select Case v
         Case "0" To "9"
            tmp = tmp & v
      End Select
   Next
   N2 = Val(tmp)
   'does Text1 contain the percent sign?
   If InStr(1, pT1, "%") Then
      N2 = N2 / 100
   End If
   tmp = Empty

   'get the comparison operator(s)
   For i = 1 To Len(pT1)
      tmp = Mid(pT1, i, 1)
      Select Case tmp
         Case "<", ">", "="
            OP = OP & tmp
      End Select
   Next

   If Len(Trim(OP)) > 0 Then
      'evaluate expression
      b1 = eval(pN1 & OP & N2)

      If b1 Then
         PassFail = "Pass"
      Else
         PassFail = "Fail"
      End If
   End If
End Function