UtterAccess Discussion Forums > Evaluating Multiple Criteria With Iif

Full Version: Evaluating Multiple Criteria With Iif

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

foxtrot123

Apr 16 2012, 12:32 PM

I have a field, Fruit, that has values like "apples", "oranges", "pears", etc. There are about 10 fruits I'd like to check for. If the value is not any of these fruits, I'd like to return a value of "Other" for a new variable, FruitOther; otherwise, return Null.

I can do this with a statement like:

CODE

FruitOther: IIf([Fruit]<>"apples" AND [OldVariable]<>"oranges" AND ... ,"Other",Null)

But this will be a very long statement. Is there a more efficient way to code / handle this? Simply being able to drop the repeating "[OldVariable]<>"'s would make for more readable syntax.

Thank you.

Bob G

Apr 16 2012, 12:42 PM

you could do a select case statement within a function and then pass that back. It might be a little neater but not anymore effective. EXCEPT, if the names of the fruits will change (add or subtract).

Would that work ?

guerillaunit

Apr 16 2012, 01:08 PM

Hi there,
Create a table with the 10 values you are checking for. Then do a outer join against this table. You can then use an if statement to check if the fruit field in this new table is null

ChiliDog

Apr 16 2012, 02:17 PM

I cannot tell what [OldVariable] is for in your post, but seems to me if you want to pursue the iif() method, it would be

iif((fruit<>"apple" and fruit<>"orange" and fruit<>"pear" etc), "Other", Null)

some people might write this

iif(not (fruit="apple" or fruit="orange" or fruit="pear" etc), "Other", Null)

they are equivalent, the second arguably more English-like.

raskew

Apr 16 2012, 06:50 PM

Hi -

Here's something you might modify to fit your needs
that may reduce code to a reasonable level.

CODE

x = "apples oranges pears bananas blackberries lemons"
y = "blueberries"
? iif(instr(x, y)>0, "found " & y, y & " not found")
blueberries not found

y = "pears"
? iif(instr(x, y)>0, "found " & y, y & " not found")
found pears

HTH - Bob

merlenicholson

Apr 16 2012, 10:05 PM

Here's an idea if you feel you must do this in code.

FruitOther: IIF( FindStr([Fruit],"Apples Oranges Pears"),Null,"Other")
Add a Public Function:

CODE

Public Function FindStr(Needle As Variant, Haystack As String) As Boolean
    Dim b() As String, I As Integer
    If IsNull(Needle) Then
        FindStr = False
        Exit Function
    End If
    b = Split(Haystack, " ")
    For I = LBound(b) To UBound(b)
        FindStr = FindStr Or Needle = b(I)
    Next
End Function

The instr() idea won't cover the situation of a [fruit] being a fractional part of a list. The Split() function I use guarantees comparison of whole words. The other advantage of using a function is dealing with Null values of [Fruit].

But Guerillaunit has the most straightforward solution.

foxtrot123

Apr 16 2012, 10:09 PM

Thank you everyone. I went with Bob G's idea of a select case statement. (And the [OldVariable] name mentioned in the original post should have read "[Fruit]".) But here I change it again! It's now OldFruit and NewFruit.

In the query I use this line:

CODE

NewFruit: EvaluateFruit(Nz([OldFruit],"Empty"))

And the function looks like this:

CODE

Public Function EvaluateFruit(A As String) As String

' If OldFruit = a fruit in the list, return the value of OldFruit
' If OldFruit <> a fruit in the list, return "Other"
Select Case A
Case Is = "Empty"
    ' Do nothing (i.e., return Null)
Case Is = "apples", "bananas", "oranges", "grapes", "pears"
    EvaluateFruit = A
Case Else
    EvaluateFruit = "Other"
End Select

End Function

I did want to keep track of all those "other" fruits. So if OldFruit has a value not in the list, I put it in another variable called, NewFruitOther, like so:

CODE

NewFruitOther: IIf([NewFruit]="Other",[OldFruit],Null)

John Vinson

Apr 17 2012, 12:18 AM

Um?

NewFruit: NZ(DLookUp("[Fruit]", "ValidFruits"), "Other")

where ValidFruits is a small onefield table with a list of all the valid fruits, will do the same thing with no VBA at all!

foxtrot123

Apr 17 2012, 09:20 PM

QUOTE (John Vinson @ Apr 17 2012, 01:18 AM)

Um?

NewFruit: NZ(DLookUp("[Fruit]", "ValidFruits"), "Other")

where ValidFruits is a small onefield table with a list of all the valid fruits, will do the same thing with no VBA at all!

I don't follow.

"ValidFruits" = the one field table that lists all the valid fruits.
"[Fruit]" = the name of the field in the ValidFruits table

Then what part of the syntax evaluates the value in OldFruit?

Just so it's clear, here's an example:

Assume there are three valid fruits: apples, bananas, and oranges.

OldFruit holds the current data. The goal is to populate the value of NewFruit based on the value of OldFruit. The desired result is:

CODE

OldFruit    NewFruit
apples        apples
apples        apples
oranges        oranges
watermelon    Other

John Vinson

Apr 17 2012, 09:44 PM

Sorry, I wasn't thinking straight.

Create a table ValidFruits with one field named Fruit. Enter three records for "Apples", "Bananas", "Oranges".

In a query based on your table containing the Oldfruit field you can put a calculated field

NewFruit: NZ(DLookUp("[Fruit]", "[ValidFruits]", "[Fruit]='" & [Oldfruit] & "'"), "Other")

This should give you what you're describing with NO further code.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.