IIF function

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Hello - I created this IIF function to evaluate a field
called Record_ID. I want to assign each record to a group
if it falls into a certain range. When I run the query
that contains this IIF function all my values are
Assigned to "Group A" Is there something wrong with my
syntax?


IIf([Record_ID]>0<=2000,"Group A",IIf([Record_ID]
=2001<=3000,"Group B",IIf([Record_ID]>=3001<=4000,"Group
Click to expand...
C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don
 
Easier than IIf, let you use "switch"


Switch([RecordId]<=2000,"Group A",[RecordId]<=3000,"Group
B",[RecordId]<=4000,"Group C",[RecordId]<=5000,"Group
D",[RecordId]>5000,"NA")



Tonín

Don said:
Hello - I created this IIF function to evaluate a field
called Record_ID. I want to assign each record to a group
if it falls into a certain range. When I run the query
that contains this IIF function all my values are
Assigned to "Group A" Is there something wrong with my
syntax?


IIf([Record_ID]>0<=2000,"Group A",IIf([Record_ID]
=2001<=3000,"Group B",IIf([Record_ID]>=3001<=4000,"Group
Click to expand...
C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don
Click to expand...
 
try
IIf([Record_ID] > 4000, "Group D", IIf([Record_ID] > 3000, "Group C",
IIf([Record_ID] > 2000, "Group B", IIf([Record_ID] > 0, "Group A", "N/A"))))
note: if you have record ids over 5000, you'll have to use Between...And...
for all the statements, instead, as
IIf([Record_ID] Between 1 And 2000, "Group A", IIf( ....
suggest you also check out the Switch() function in Access Help, to see if
it might work better than nested IIf() functions.

hth


Don said:
Hello - I created this IIF function to evaluate a field
called Record_ID. I want to assign each record to a group
if it falls into a certain range. When I run the query
that contains this IIF function all my values are
Assigned to "Group A" Is there something wrong with my
syntax?


IIf([Record_ID]>0<=2000,"Group A",IIf([Record_ID]
=2001<=3000,"Group B",IIf([Record_ID]>=3001<=4000,"Group
Click to expand...
C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don
Click to expand...
 
Tonin is correct, Switch would probably be easier. The Switch function will
use the first True value it finds, moving from Left to Right through the
list. Therefore, if you have <=2000 followed by <=3000, the >=2001 is
implied because it will never get to the second one if the first one is
true. To add in your >0 option, make it the first one and set the value to
"" or "Error", whichever you prefer. Modifying Tonin's function for this it
would be:

Switch([Record_ID]<=0, "Error", [Record_Id]<=2000,"Group
A",[Record_Id]<=3000,"Group B",[Record_Id]<=4000,"Group
C",[Record_Id]<=5000,"Group D",[Record_Id]>5000,"NA")

or

Switch([Record_ID]<=0, "", [Record_Id]<=2000,"Group
A",[Record_Id]<=3000,"Group B",[Record_Id]<=4000,"Group
C",[Record_Id]<=5000,"Group D",[Record_Id]>5000,"NA")


However, for future reference, there is a syntax problem.
IIf([Record_ID]>0<=2000,"Group A",IIf([Record_ID]
Click to expand...

You have to spell out both parts if you want the value to meet two criteria.
In this case you want Record_ID to be greater than 0 and less than or equal
to 2000. This should be depicted as

IIf([Record_ID]>0 And [Record_ID]<=2000, "Group A", IIf(......

--
Wayne Morgan
Microsoft Access MVP


Don said:
Hello - I created this IIF function to evaluate a field
called Record_ID. I want to assign each record to a group
if it falls into a certain range. When I run the query
that contains this IIF function all my values are
Assigned to "Group A" Is there something wrong with my
syntax?


IIf([Record_ID]>0<=2000,"Group A",IIf([Record_ID]
=2001<=3000,"Group B",IIf([Record_ID]>=3001<=4000,"Group
Click to expand...
C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don
Click to expand...
 
BTW, if you are working in design view, then change all commas "," into
semicolons ";"
:-) as follows:

Switch([RecordId]<=2000;"Group A";[RecordId]<=3000;"Group
B";[RecordId]<=4000;"Group C";[RecordId]<=5000;"Group
D";[RecordId]>5000;"NA")

Regarding to your original "IIf" expression:
"IIf([Record_ID]>0<=2000;IIf(...

that is not OK. Try:
"IIf(([Record_ID]>0) And ([Record_ID]<=2000);IIf(... and so on.


Tonín

Tonín said:
Easier than IIf, let you use "switch"


Switch([RecordId]<=2000,"Group A",[RecordId]<=3000,"Group
B",[RecordId]<=4000,"Group C",[RecordId]<=5000,"Group
D",[RecordId]>5000,"NA")



Tonín

Don said:
Hello - I created this IIF function to evaluate a field
called Record_ID. I want to assign each record to a group
if it falls into a certain range. When I run the query
that contains this IIF function all my values are
Assigned to "Group A" Is there something wrong with my
syntax?


IIf([Record_ID]>0<=2000,"Group A",IIf([Record_ID]
=2001<=3000,"Group B",IIf([Record_ID]>=3001<=4000,"Group
Click to expand...
C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don
Click to expand...
Click to expand...
 
Back
Top