IIF function

[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

C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don

 

[Tonín]

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

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

C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don

 

[tina]

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

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

C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don

 

[Wayne Morgan]

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]

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

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

C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don

 

[Tonín]

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

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

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

C",IIf([Record_ID]>=4001<=5000,"Group D","NA"))))


Thanks
Don