Nested IIF

S

scubadiver

Hi folks!

I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 – Group 1
£45-£55 – Group 2
£55-£65 – Group 3
£65-£75 – Group 4
£75- – Group 5

The bottom limit should be "<=" and the uipper limit should be "<". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35<=[Ave1]<45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.
 
J

Jason Lepack

This should do it!

iif([avel]<35,0,iif([avel]<45,1,iif([avel]<55,2,iif([avel]<65,3,iif([avel]<75,4,5)))))
 
S

scubadiver

Cheers! :)


Jason Lepack said:
This should do it!

iif([avel]<35,0,iif([avel]<45,1,iif([avel]<55,2,iif([avel]<65,3,iif([avel]<75,4,5)))))
I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 - Group 1
£45-£55 - Group 2
£55-£65 - Group 3
£65-£75 - Group 4
£75- - Group 5

The bottom limit should be "<=" and the uipper limit should be "<". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35<=[Ave1]<45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.
Click to expand...
Click to expand...
 
J

Jerry Whittle

Instead of a hard to maintain nested IIf statement, consider using a Case
statement. Paste the following in a module and compile it.

Function fGroups(strGroups As Variant) As Long
Dim TheGroups As Long
Select Case strGroups
Case 35 To 44.99
TheGroups = 1
Case 45 To 54.99
TheGroups = 2
Case 55 To 64.99
TheGroups = 2
Case 65To 74.99
TheGroups = 4
Case Is >= 75
TheGroups = 5
Case Else ' Other values.
TheGroups = 0
End Select
fGroups = TheGroups
End Function

In your query, just put the following:

fGroups

Also you really shouldn't create a new field in a table if that's what you
mean. Any time that you want to see the group, use the function above.
 
J

Jerry Whittle

Strange. Some of what I typed in is missing.

TheGroup: fGroups([ave1])

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jerry Whittle said:
Instead of a hard to maintain nested IIf statement, consider using a Case
statement. Paste the following in a module and compile it.

Function fGroups(strGroups As Variant) As Long
Dim TheGroups As Long
Select Case strGroups
Case 35 To 44.99
TheGroups = 1
Case 45 To 54.99
TheGroups = 2
Case 55 To 64.99
TheGroups = 2
Case 65To 74.99
TheGroups = 4
Case Is >= 75
TheGroups = 5
Case Else ' Other values.
TheGroups = 0
End Select
fGroups = TheGroups
End Function

In your query, just put the following:

fGroups

Also you really shouldn't create a new field in a table if that's what you
mean. Any time that you want to see the group, use the function above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


scubadiver said:
Hi folks!

I have a currency field in a query (called "ave1") and I want to create a
classification field based on the following so the new field should consist
of the numbers 1 to 5:

£35-£45 – Group 1
£45-£55 – Group 2
£55-£65 – Group 3
£65-£75 – Group 4
£75- – Group 5

The bottom limit should be "<=" and the uipper limit should be "<". Group 5
should be £75 or higher.

As a test I have the following:

Expr1: IIf(35<=[Ave1]<45,1,0).

Is this correct? Or should there be an "OR" statement in there as well?

cheers.
Click to expand...
Click to expand...
 
J

Jerry Whittle

Jamie said: "But because this is a group about SQL DBMS, how about using *data*
rather than hard-coding the logic into the front end or the schema
(hint: inflation may cause the groups' limits to change): "

No doubt that your solution is ultimately the most correct. I hate long
nested IIf's, lines of Case statements, and in Oracle, long Decode statements.
 
Top