IIf Condition with 3 Conditions

[sajarac]

Hope someone can help me. I have two fields one is a droplist with Open and Closed choices, and I have another date field, I would like to add the following conditions:

1. If the field 1 is "Closed" and the date field has a date show "Won"
2. If the field 1 is "Closed" and the date field is empty show "Lost"
3. Is the field 1 is "Open" and the date field is empty show "Blanc" or leave empty.

is this possible to achieve?

Many thanks for you cooperation.

Regards

Sajarac

 

[Ranman256]

build a custom function and you never have to use the if statement in any other query.
in a module, past this code

Public Function getWLCode(ByVal pvField, ByVal pvDate)
Select Case True
   Case pvField = "Closed" And Not IsNull(pvDate)
      getWLCode = "Won"
   Case pvField = "Closed" And IsNull(pvDate)
      getWLCode = "Lost"
   Case Else
      getWLCode = ""
End Select
End Function

then in your query send it the 2 fields, to get the result
getWLCode([field1], [DateFld])

 

[arnelgp]

=IIF(field1 = "Closed", IIF(dateField & "" = "", "Won", "Lost"), IIF(field1 = "Open", IIF(dateField & "" = "", "Blanc", Null))

 

[sajarac]

Opps, sorry just changed the Won Lots position and it works great.!!!

Many thnaks and many respects

Master!!