IIF issue "newbie"

[Laura]

I have never put together an "if" statement before, I have read through the
posts but cannot find one close to the statement I am trying to create. This
is what I have put together but no matter what the answer is, it defaults to
1. Please help me - thank you.

=IIf([Avg of CBCC]<34.99,"1",IIf([Avg of CBCC]<42.99,"2",IIf([Avg of
CBCC]<49.99,"3",IIf([Avg of CBCC]<59.99,"4",IIf([Avg of CBCC]>59.99,"5")))))

Actually the criteria is as follows: to rate a "1" it is less than 35, to
rate a "2" it is between 35 and 42.99, to rate a "3" it is between 43 and
49.99, to rate a "4" it is between 50 and 59.99, and to rate a five it is
greater then 60.

 

[Duane Hookom]

I would never create an expression like this since the ratings may change
and I would hate to find and maintain complex IIf()s that might be contained
in queries or reports.

Consider creating a lookup table with min and max CBCC values and the
related rating. Otherwise create a user defined function that can be easily
maintained when your scale changes

1) Open a new, blank module and copy this code into it:

Public Function GetCBCCRating(dblCBCC As Double) _
As Integer
Select Case dblCBCC
Case Is < 35
GetCBCCRating = 1
Case Is < 43
GetCBCCRating = 2
Case Is < 50
GetCBCCRating = 3
Case Is < 60
GetCBCCRating = 4
Case Else
GetCBCCRating = 5
End Select
End Function

2) Then save the module as "modBusinessCalcs".
3) You can then use this function in your report or query like

Query
Rating: GetCBCCRating([Avg of CBCC])

Text box control source
Control Source: =GetCBCCRating([Avg of CBCC])

 

[John Spencer]

If those are percent figures, you might want to move the decimal point over
two places.

34.99 is 3499 percent
..3499 is 34.99 percent

I would never create an expression like this since the ratings may change
and I would hate to find and maintain complex IIf()s that might be
contained in queries or reports.

Consider creating a lookup table with min and max CBCC values and the
related rating. Otherwise create a user defined function that can be
easily maintained when your scale changes

1) Open a new, blank module and copy this code into it:

Public Function GetCBCCRating(dblCBCC As Double) _
As Integer
Select Case dblCBCC
Case Is < 35
GetCBCCRating = 1
Case Is < 43
GetCBCCRating = 2
Case Is < 50
GetCBCCRating = 3
Case Is < 60
GetCBCCRating = 4
Case Else
GetCBCCRating = 5
End Select
End Function

2) Then save the module as "modBusinessCalcs".
3) You can then use this function in your report or query like

Query
Rating: GetCBCCRating([Avg of CBCC])

Text box control source
Control Source: =GetCBCCRating([Avg of CBCC])

--
Duane Hookom
MS Access MVP

I have never put together an "if" statement before, I have read through
the
posts but cannot find one close to the statement I am trying to create.
This
is what I have put together but no matter what the answer is, it defaults
to
1. Please help me - thank you.

=IIf([Avg of CBCC]<34.99,"1",IIf([Avg of CBCC]<42.99,"2",IIf([Avg of
CBCC]<49.99,"3",IIf([Avg of CBCC]<59.99,"4",IIf([Avg of
CBCC]>59.99,"5")))))

Actually the criteria is as follows: to rate a "1" it is less than 35, to
rate a "2" it is between 35 and 42.99, to rate a "3" it is between 43 and
49.99, to rate a "4" it is between 50 and 59.99, and to rate a five it is
greater then 60.

 

[OfficeDev18 via AccessMonster.com]

Laura,

While you're getting help from two of the heaviest hitters that patrol this
site, and you certainly don't need my two cents, I can't help putting in
exactly two cents worth: have you checked your math, to make sure that [Avg
of CBCC] can vary? Check your data; something might be sticking it
artificially at that level.

Sam

If those are percent figures, you might want to move the decimal point over
two places.

34.99 is 3499 percent
.3499 is 34.99 percent

I would never create an expression like this since the ratings may change
and I would hate to find and maintain complex IIf()s that might be

[quoted text clipped - 47 lines]