Problem with Expression, >=7 And <=24

[number2]

On an unbound text field I am trying to define a catagory based on the value of another field. for example, if the value of field [step7totpri]is between 7 and 24, then its catagory is "1, Mild".

I can not get this expression to work:

=IIf([step7totpri]>=7 And <=24,"1, Mild"," ") & IIf([step7totpri]>=25 And <=42,"2, Moderate"," ") & IIf([step7totpri]>=43 And <=60,"3, Moderately Severe"," ") & IIf([step7totpri]>=61,"4, Severe"," ")

Any ideas?

 

[jrbarnett]

Hi

There are two ways of approaching this:
1) to put the fieldname after the "And" statement, eg:

=IIf([step7totpri]>=7 And [step7totpri] <=24,"1, Mild"," ") & IIf([step7totpri]>=25 And [step7totpri] <=42,"2, Moderate"," ")

or to use the Between keyword:

=IIf([step7totpri] Between 7 And 24,"1, Mild"," ") & IIf([step7totpri] Between 25 And 42,"2, Moderate"," ")

John

 

[TheAceMan1]

How are ya number2 . . . .

Your concatenateing [purple]IIF[/purple] expressions. Although it wont hurt anything, its a bad habit to get into. Could cause you problems in the future! Here's the correct syntax for categorizing:

[blue]IIf (([step7totpri]>=7 And ([step7totpri]<=24, "1,Mild", IIf (([step7totpri]>=25 And ([step7totpri]<=42, "2,Moderate", IIf (([step7totpri]>=43 And ([step7totpri]<=60, "3,Moderate Severe", IIf (([step7totpri]>=61, "4Severe", "") ) ) )[/blue]

See Ya! . . . . . .

 

[number2]

Thanks for all of the help! What type of problems might such IIf statement concatenation cause?

 

[TheAceMan1]

number2 . . . .

If you consider that each IIF returns something (true or false per the expression), and you concatenate them, your putting together 4 answers for your return value. Per your example, consider all expressions return False, then your return would be:

[blue]" " & " " & " " & " "[/blue], or blue" "[/blue] 4 spaces!

Consider you had some text for false in each IIF, and only the second IIF was true, then your return would be:

[blue]"FalseText1" & "Moderate" & "Falsetext3" & "FalseText4"
or
"FalseText1ModerateFalseText3FalseText4"[/blue]

Although this is not illegal as far as Access is concerned, it is illegal as far as what your looking for.

Normally IIF's are nested (as my example) to return one value. If you notice, additional IIf's are added to the False part of the previous IIF. Nesting is carried thru until the last expression is evaluated.

The biggest problem with nesting is keeping track of all the parentheses.

See Ya! . . . . . .