IIf Statement

[lindag]

Any suggestions on making this formula work?

IIf(CITY like “Phila*” And BALANCE<10,000,”Phila < 10,000”,IIf(CITY
like “Phila*” And BALANCE >10,000,”Phila > 10,000”,
IIf(CITY not like “Phila*” And BALANCE<8,000,”Phila < 8,000”,IIf(CITY
not like “Phila*” And BALANCE >8,000,”Phila > 8,000”,”Unknown”))))

 

[Ron2006]

It would help to know what you really want to test to tell you. (In
some other words, not the if statement.)

For one thing: if the city is like Phila and the balance = 10000
the result will be "Unknown"
OR if the city is NOT like Phila and the balance =
8000 the result will be "Unknown"

 

[John W. Vinson]

Any suggestions on making this formula work?

IIf(CITY like “Phila*” And BALANCE<10,000,”Phila < 10,000”,IIf(CITY
like “Phila*” And BALANCE >10,000,”Phila > 10,000”,
IIf(CITY not like “Phila*” And BALANCE<8,000,”Phila < 8,000”,IIf(CITY
not like “Phila*” And BALANCE >8,000,”Phila > 8,000”,”Unknown”))))

In what way does it not work? I'd remove the commas from the numbers in the
criteria but other than that it should work.

If you have more othan four options you might want to instead use the Switch()
function. It takes arguments in pairs, evaluates the pairs left to right, and
when it finds a TRUE value as the first element of a pair it returns the
second element and quits:

Switch(CITY LIKE "Phila*" AND BALANCE < 10000, "Phila < 10,000",
CITY LIKE "Phila* AND BALANCE >= 10000, "Phila 10,000 and over",
<etc>,
TRUE, "Unknown")

 

[Duane Hookom]

IMO, it's a mistake to hard-code values like Phila, 10000 and 8000 into an
expression in a query. Next week someone is going to want this to change to
Philad , 12000, and 9000.

I would minimally create a nice little user defined function in a public
module.

Untested function:
Public Function GetCityBalance( strCity as String, dblBalance as Double) as
String
Dim lngHiNum as Long
Dim lngLoNum as Long
Dim strCityLeft as String
Dim intLen as integer
Dim strUNK as String
'==============================
' change these whenever and keep them at the
' top where you can find them
'==============================
lngHiNum = 10000
lngLoNum = 8000
strCityLeft = "Phila"
strUNK = "Unknown"
'==============================
intLen = Len(strCityLeft)
Select Case True
Case Left(strCity,intLen) = strCityLeft AND dblBalance < lngHiNum
GetCityBalance = strCityLeft & " < " & lngHiNum
Case Left(strCity,intLen) = strCityLeft AND dblBalance >= lngHiNum
GetCityBalance = strCityLeft & " >= " & lngHiNum
Case Left(strCity,intLen) <> strCityLeft AND dblBalance < lngLoNum
GetCityBalance = strCityLeft & " < " & lngLoNum
Case Else
GetCityBalance = strUNK
End Select
End Function

Save the function in a standard module named "modBusinessCalcs". You can
then use the function everywhere and when you

 

[lindag]

I forgot to indicate the purpose. Thank you.

IIf(CITY like “Phila*” And BALANCE<10,000,”Phila < 10,000”, "Phila
less than 10,000",
IIf(CITY like “Phila*” And BALANCE >10,000,”Phila > 10,000”, "Phila
greater than 10,000",
IIf(CITY not like “Phila*” And BALANCE<8,000,”Phila < 8,000”, "No
Phila less than 8,000",
IIf(CITY not like “Phila*” And BALANCE >8,000,”Phila > 8,000”,”No
Phila greater than 8,000”))))

 

[Ron2006]

And I repeat my question:

What do you want if the city is like Phila* and the Balance is equal
to 10000?

and similar for the not like Phila* and the balance is equal to 8000?

A value of unknown in those situations doesn't seem reasonable.

As suggested, take the commas OUT of the amount field of the
comparison.

===========
The last line of
IIf(CITY like “Phila*” And BALANCE<10000,”Phila < 10,000”, "Phila
less than 10,000",
IIf(CITY like “Phila*” And BALANCE >10000,”Phila > 10,000”, "Phila
greater than 10,000",
IIf(CITY not like “Phila*” And BALANCE<8000,”Phila < 8,000”, "No
Phila less than 8,000",
IIf(CITY not like “Phila*” And BALANCE >8000,”Phila > 8,000”,”No
Phila greater than 8,000”))))

should be

Phila greater than 8,000”,"Unknown"))))




Ron