Complex IIF

[nicpkh via AccessMonster.com]

I want to use this If statement on my query but then it said complex if
statement. Any suggestion on how can i use this condition on my query. Thanks


IIf([TotalUnit]>=3795,24,
IIf([TotalUnit]>=3690,23,
IIf([TotalUnit]>=3585,22,
IIf([TotalUnit]>=3480,21,
IIf([TotalUnit]>=3375,20,
IIf([TotalUnit]>=3270,19,
IIf([TotalUnit]>=3165,18,
IIf([TotalUnit]>=3060,17,
IIf([TotalUnit]>=2955,16,
IIf([TotalUnit]>=2850,15,
IIf([TotalUnit]>=2745,14,
IIf([TotalUnit]>=2640,13,
IIf([TotalUnit]>=2535,12,
IIf([TotalUnit]>=2430,11,
IIf([TotalUnit]>=2325,10,
IIf([TotalUnit]>=2220,9,
IIf([TotalUnit]>=2115,8,
IIf([TotalUnit]>=2010,7,
IIf([TotalUnit]>=1905,6,
IIf([TotalUnit]<=1800,5,5))))))))))))))))))))

 

[John W. Vinson]

I want to use this If statement on my query but then it said complex if
statement. Any suggestion on how can i use this condition on my query. Thanks


IIf([TotalUnit]>=3795,24,
IIf([TotalUnit]>=3690,23,
IIf([TotalUnit]>=3585,22,
IIf([TotalUnit]>=3480,21,
IIf([TotalUnit]>=3375,20,
IIf([TotalUnit]>=3270,19,
IIf([TotalUnit]>=3165,18,
IIf([TotalUnit]>=3060,17,
IIf([TotalUnit]>=2955,16,
IIf([TotalUnit]>=2850,15,
IIf([TotalUnit]>=2745,14,
IIf([TotalUnit]>=2640,13,
IIf([TotalUnit]>=2535,12,
IIf([TotalUnit]>=2430,11,
IIf([TotalUnit]>=2325,10,
IIf([TotalUnit]>=2220,9,
IIf([TotalUnit]>=2115,8,
IIf([TotalUnit]>=2010,7,
IIf([TotalUnit]>=1905,6,
IIf([TotalUnit]<=1800,5,5))))))))))))))))))))

Don't use IIF.

The ideal solution would be to use a table with the range and the result for
that range. Use a Query to find the desired record based on your TotalUnit
field. If you want to do it in code, use the Switch() function instead; it
takes arguments in pairs, evaluates them left to right, and when it finds a
pair for which the first member is TRUE it returns the second member:

Switch([TotalUnit] >= 3795, 24, [TotalUnit] >= 3690, 23, <etc>, [TotalUnit] >=
1905, 6, True, 5)

 

[nicpkh via AccessMonster.com]

Hi John,

Thanks for the suggestion.

Let say I want to use the method you mention by using table with the range
and the result. How do I do it? Sorry for the troublesome.

I want to use this If statement on my query but then it said complex if
statement. Any suggestion on how can i use this condition on my query. Thanks

[quoted text clipped - 19 lines]

IIf([TotalUnit]>=1905,6,
IIf([TotalUnit]<=1800,5,5))))))))))))))))))))

Don't use IIF.

The ideal solution would be to use a table with the range and the result for
that range. Use a Query to find the desired record based on your TotalUnit
field. If you want to do it in code, use the Switch() function instead; it
takes arguments in pairs, evaluates them left to right, and when it finds a
pair for which the first member is TRUE it returns the second member:

Switch([TotalUnit] >= 3795, 24, [TotalUnit] >= 3690, 23, <etc>, [TotalUnit] >=
1905, 6, True, 5)

 

[Piet Linden]

Hi John,

Thanks for the suggestion.

Let say I want to use the method you mention by using table with the range
and the result. How do I do it? Sorry for the troublesome.

You would have a table of value pairs. Since you don't give any names
for anything, they'd be like this:
(3795,24)
(3690,23)
(3585,22)

Something like this maybe:

SELECT TOP 1 xlsValues.Miles, xlsValues.Grouping
FROM xlsValues
WHERE (((xlsValues.Miles)<=[Enter a value:]));

 

[vanderghast]

Assuming table name is "categories" with fields "totalUnitLimit" and
"category" where the category is an increasing value as the total unit
limit increase, then:


SELECT totalUnit, MAX(category)
FROM tableName INNER JOIN categories
ON tableName.totalUnit >= categories.TotalUnitLimit
GROUP BY totalUnit



Otherwise, a subquery could also do the job, in a more complex case.



Vanderghaast, Access MVP

Hi John,

Thanks for the suggestion.

Let say I want to use the method you mention by using table with the range
and the result. How do I do it? Sorry for the troublesome.

I want to use this If statement on my query but then it said complex if
statement. Any suggestion on how can i use this condition on my query.
Thanks

[quoted text clipped - 19 lines]

IIf([TotalUnit]>=1905,6,
IIf([TotalUnit]<=1800,5,5))))))))))))))))))))

Don't use IIF.

The ideal solution would be to use a table with the range and the result
for
that range. Use a Query to find the desired record based on your TotalUnit
field. If you want to do it in code, use the Switch() function instead; it
takes arguments in pairs, evaluates them left to right, and when it finds
a
pair for which the first member is TRUE it returns the second member:

Switch([TotalUnit] >= 3795, 24, [TotalUnit] >= 3690, 23, <etc>,
[TotalUnit] >=
1905, 6, True, 5)

 

[vanderghast]

And here is a possible solution with joins, in the case where the categories
would not be 'increasing" as the total unit limit increase:


SELECT a.totalUnit, LAST(b.category)
FROM (tableName AS a INNER JOIN categories AS b
ON a.totalUnit >= b.totalLevelUnit) INNER JOIN categories AS c
ON a.totalUnit >= c.totalLevelUnit
GROUP BY a.totalUnit, b.totalLevelUnit
HAVING b.totalLevelUnit=MAX(c.totalLevelUnit)




Note that alias "b" and "c" are the same, if we only look at the FROM
clause, but the GROUP occurs only on "b", so "c" is free and once the groups
are made, we only keep the b-group which matches the maximum value for
totalLevelUnit.



Vanderghast, Access MVP

Assuming table name is "categories" with fields "totalUnitLimit" and
"category" where the category is an increasing value as the total unit
limit increase, then:


SELECT totalUnit, MAX(category)
FROM tableName INNER JOIN categories
ON tableName.totalUnit >= categories.TotalUnitLimit
GROUP BY totalUnit



Otherwise, a subquery could also do the job, in a more complex case.



Vanderghaast, Access MVP

Hi John,

Thanks for the suggestion.

Let say I want to use the method you mention by using table with the
range
and the result. How do I do it? Sorry for the troublesome.

I want to use this If statement on my query but then it said complex if
statement. Any suggestion on how can i use this condition on my query.
Thanks
[quoted text clipped - 19 lines]
IIf([TotalUnit]>=1905,6,
IIf([TotalUnit]<=1800,5,5))))))))))))))))))))

Don't use IIF.

The ideal solution would be to use a table with the range and the result
for
that range. Use a Query to find the desired record based on your
TotalUnit
field. If you want to do it in code, use the Switch() function instead;
it
takes arguments in pairs, evaluates them left to right, and when it finds
a
pair for which the first member is TRUE it returns the second member:

Switch([TotalUnit] >= 3795, 24, [TotalUnit] >= 3690, 23, <etc>,
[TotalUnit] >=
1905, 6, True, 5)

 

[John Spencer]

You have several suggestions, but since you have a regular set of values
you can do math to get the results. The math would probably be
something like the following.

IIF(TotalUnit>=3795,24,
IIF(TotalUnit<1800,5,
5+((TotalUnit-1800)\105)))

As noted elsewhere, having a table of the ranges might work better for
you. Especially if your ranges can change.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Nick via AccessMonster.com]

You have several suggestions, but since you have a regular set of values
you can do math to get the results. The math would probably be
something like the following.

IIF(TotalUnit>=3795,24,
IIF(TotalUnit<1800,5,
5+((TotalUnit-1800)\105)))

As noted elsewhere, having a table of the ranges might work better for
you. Especially if your ranges can change.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I want to use this If statement on my query but then it said complex if
statement. Any suggestion on how can i use this condition on my query. Thanks

[quoted text clipped - 19 lines]

IIf([TotalUnit]>=1905,6,
IIf([TotalUnit]<=1800,5,5))))))))))))))))))))

Hi All

It working now. Thanks everybody for your suggestions. It really helpful and
I learn few tricks as well. Thanks