Nested IIF statements in a query expression

[Greek]

In an expression, I am getting a syntax error message when I try to run the query with nested IFF statements involving numbers in different ranges. Would anyone please help me with the correct syntax. Thanks very much.


=IIF([TableA]>-1,"On or Ahead of Target", IIF([TableA] is between -1<and>-9,"Behind 1-9 Days", IIF([TableA] is between -10<and>-14,"Behind Two Weeks", "Over Three Weeks Behind")))

 

[plog]

It's how you are using your Between. Remove the greater than/less than signs around and:

IIF([TableA] is between -1 and -9

 

[Greek]

Thanks again, Plog.

 

[Greek]

I tried Pog's suggestion:

IIF([TableA] is between -1 and -9

And I am still getting an incorrect syntax message. Here is what I tried:

IIF([TableA] is Between -1 and -9, "Behind Schedule 1-9 Days", IIF([TableA] is Between -10 and -14, "Behind Schedule 10-14 Days", IIF(TableA] is Between -15 and -19, "Behind Schedule 15-19 Days", "Behind 20+ Days")))

What I am trying to accomplish is to be able to group 500+ students in an appropriate performance category, where different remedial action is taken for different ranges.

Any help will be appreciated. Andy

 

[spikepl]

When coding, don't do too many things at the same time, because you cannot tell what is biting you. Also, make use of the resources available to you:

1. Make one IIF work. Once that works, nest another one, just one, and so on.
2. Look up the documentation for use of BETWEEN as criterion in Access SQL

 

[jdraw]

What do you mean iif TableA??? You need a specific field in that table --- unless your fields are named TableA....
Need more info to be helpful.

 

[Greek]

Thanks, Spikepl.

I did make on IIF statement work (the first one). The IIFs that follow are all ranges between two negative numbers, and that's where I get the syntax error. I've also looked up examples of proper syntax involving Between conditions, and tried them. It's got to be something I am doing. Thanks again. A

 

[spikepl]

Yup.

You need to follow the examples of Between usage 100%. Find one where Between is shown in an SQL statement and follow it carefully character by character.

 

[Greek]

Thanks, jdraw.

"Table A" is a calculated field of a student's performance in number of days. For example, a 3 means the student is three days ahead of target. A -5 for a student means the student is five days behind target. So, if Table A=-5, I want my expression to place that student in the category of "Behind 1-9 days" (the second IIF statement above), and so forth for 500 students.

Thanks again. Andy

 

[Greek]

Thanks spikepl for the help. I will do that. Appreciate your help. Andy

 

[Greek]

I figured it out: Between statements cannot have IS in the syntax. For example, "is Between 50 and 100" will give you the syntax error. Remove the 'is' word and it works. Even a blind squirrel finds an acorn once in a while.

 

[spikepl]

Exactamente.

Kudos to blind squirrels

 

[Greek]

Looking for that 'IS' caused me to miss the Man City upset. But, it was worth it.