IFF statement in query with multiple outcomes

[timmygrover]

I've seen this issue KINDA addressed elsewhere, but the answer usually seems to be, "Well, it'd be easier if you did something completely different." But I would actually like to know how to do it, and I'm sure there are others who would as well.



SO

Lets say you've got a field in a table called [favorite food] and you've got one person that entered BEEF, one that entered APPLE, and one that entered CARROT. And you'd like to make a query that outputs the type of food MEAT, FRUIT or VEGETABLE.

How would you craft your IFF statement to output Meat if it's Beef, Fruit if it's Apple, or Vegetable if it's Carrot?

IFF([favorite food]="BEEF", "MEAT", OR IFF([favorite food]="APPLE", "FRUIT", OR IFF[favorite food]="CARROT", "VEGETABLE")

I know that's not right, but I don't know what would be either. Please help if you can! Thanks in advance

[pbaldy]

For starters, it's IIf, not IFF. It would be along the lines of:

IIf([favorite food]="BEEF", "MEAT", IIf([favorite food]="APPLE", "FRUIT", IIf([favorite food]="CARROT", "VEGETABLE")))

[RayMilhon]

Personally, I would do it in VBA easier to program and modify Assuming that Beef, Apple and Carrot aren't the only choices your IIF could get unmanageable

Public Fuction foodtype(ByRef strvalue as string) as string

Select Case strvalue
case is = "Beef", Is = "Chicken", Is = "Pork"
foodtype = "Meat"
Case is = "Apple",is = "orange", Is = "Pear"
foodtype = "Fruit"
Case is = "Carrot", "Peas", "Corn"
foodtype = "Vegetable"
Case Else
foodtype = "Other"
End select

End function

[pbaldy]

So would I (actually I'd have it in a table), but:

I've seen this issue KINDA addressed elsewhere, but the answer usually seems to be, "Well, it'd be easier if you did something completely different." But I would actually like to know how to do it, and I'm sure there are others who would as well.

[timmygrover]

@ pbaldy, sorry, you're right, (iif, not iff). I just popped it out really quick, didn't proof.

[timmygrover]

Also, the situations in which I'd like to use these are actually much more specific than the food group examples i gave. I would actually like to know the construction to properly complete this type of statement.

[pbaldy]

I would actually like to know the construction to properly complete this type of statement.

I believe I gave you that in post 2, did I not?

[timmygrover]

Oops, you did! I was so busy feeling silly for iff/iif that I didn't even notice that you'd done it. Thanks so much for your help!

[pbaldy]

No problemo!

[RayMilhon]

Hi Paul, I knew that's not what he asked but you had already answered it and I thought I'd point out there are better ways to get the result he asked for. Best way as you pointed out is a table.