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:
And the function looks like this:
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
EvaluateFruit = "Other"
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: