Return a value to a field based on more than two conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am familiar with IIf statements, but does anyone know how to nest these
statement, or a better solution e.g
=IIf([Number]=1,"one") Or
=IIf([Number]=]=2,"two") Or
=IIf([Number]=]=3,"three")
 
Number:
IIf([Table1]![Field1]=1,"One",IIf([Table1]![Field1]=2,"Two",IIf([Table1]![Field1]=3,"Three")))
 
All you need to do is replace the "falsepart" parameter with another iif
statement.

Depending on how many values you're looking at, you could end up with a
pretty ugly statement. It may be worthwhile to create a function like:

Function get_number_name(inNumber As Integer)
Select Case inNumber
Case 1: get_number_name = "one"
Case 2: get_number_name = "two"
Case 3: get_number_name = "three"
Case 4: get_number_name = "four"
Case 5: get_number_name = "five"
Case 6: get_number_name = "six"
Case 7: get_number_name = "seven"
Case 8: get_number_name = "eight"
Case 9: get_number_name = "nine"
Case 0: get_number_name = "zero"
Case Else: get_number_name = "ERROR"
End Select
End Function
 
I am familiar with IIf statements, but does anyone know how to nest these
statement, or a better solution e.g
=IIf([Number]=1,"one") Or
=IIf([Number]=]=2,"two") Or
=IIf([Number]=]=3,"three")
Click to expand...

The Choose() function is one option: it takes an arbitrary number of
arguments; evaluates the first as an integer; and then uses that integer as an
index to the remaining arguments. In your case

NumberName: Choose([Number], "one", "two", "three", "four", <etc etc>)

Alternatively you could set up a small translation table with two fields,
Number and NumberName, and just join it to your query. This would be the
better alternative if you are going much beyond ten.

John W. Vinson [MVP]
 
Back
Top