Limit on iif statements?

G

Guest

I have a query make-table query that works. However, I need to add one more
if statement and when I do, along with the additional left ), I get a message
"Query too Complex..."

Is there a limit on the number of if statements Access2003 can handle?

Here is the query that works:

SELECT tg.ImportID, tg.App_ID, tg.Equipment_ID, tg.ItemID, tg.Manufacturer1,
Manufacturer2, tg.Model_No1, tg.BTUH, tg.System, tg.Product, tg.Cooling,
tg.EER, tg.SEER, tg.IPLV, tg.HighCOP, tg.LowCOP, tg.HSPF, tg.TXV, tg.ESTier,
tg.ESRebate_AmtH, tg.ESRebate_Per_Ton, ESApprove_FlagH, IIf(([Product]="AC"
Or [Product]="HP") And ([System]="S" Or [System]="P"),


IIF(([Cooling]="W" AND [EER]>=14 AND [BTUH] >=135000), "S213",

IIF(([Cooling]="W" AND [EER]>=14 AND [BTUH] <135000), "S216",

IIf([BTUH]>240000 AND [IPLV]>=10.4,"S218",

IIf(([BTUH]>=135000 And [BTUH]<=240000) AND [EER]>=10.8 AND
[IPLV]>=11.2,"S217",

IIf(([BTUH]>=65000 And [BTUH]<135000 AND [EER]>=11),"S216",

IIf(([BTUH]<65000 AND ( [EER]>=12 Or [SEER]>=14) And [TXV]=True) and
[Cooling]="A","S181",

IIf(([BTUH]<65000 AND ([EER]>=11.6 Or [SEER]>=14) And [System]="P" and
[Cooling]="A"),"S182",

IIf(([BTUH]<65000 AND (([EER]>=11.6 AND [EER]<12) Or ([SEER]>=13 AND
[SEER]<14)) And [TXV]=True),"S214",

IIf(([BTUH]<65000 AND (([EER]>=11.3 AND [EER]<11.6) Or ([SEER]>=13 AND
[SEER]<14)) And [System]="P" And [Cooling]="A"),"S215",

IIf(( [BTUH]<65000 AND [Cooling]="A" AND (([EER]>=11 AND [EER]<11.3) Or
([SEER]>=13)) And [System]="S" ),"S104",

IIf(([BTUH]<65000 AND ([EER]>=11 Or [SEER]>=12) And nz([SEER],0)<13 And
[System]="P" ) AND [Cooling]="A","S105",

"NOT YET")))))))))))) AS Measure, IIf([Measure] In ('S104','S105'),1,

IIf([Measure] In ('S213','S214','S215','S216','S217','S218'),2,

IIf([Measure] In ('S181','S182'),3,0))) AS Tier, IIf([Measure] In
('S104','S105'),Format(76,"Currency"),
IIf([Measure] In ('S214','S215'),Format(95,"Currency"),
IIf([Measure] In ('S181','S182'),Format(132,"Currency"),

IIf([Measure] In ('S213'),Format(107,"Currency"),

IIf([Measure] In ('S216'),Format(71,"Currency"),
IIf(([Measure] In ('S217','S218') And
[Cooling]="A"),Format(71,"Currency"),0)))))) AS Rebate_Per_Ton INTO
acQUALIFIEDS
FROM acSAMPLE_APPLICATION AS tg;
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You may want to use the Switch() function instead of the nested IIf()s:

IIf(Product IN ("AC","HP") And [System] IN ("S" "P"),
Switch(Cooling="W" AND EER>=14 AND BTUH>=135000, "S213",
Cooling="W" AND EER>=14 AND BTUH<135000, "S216",
BTUH>240000 AND IPLV>=10.4, "S218",
... etc. ... ,
BTUH<65000 AND (EER>=11 Or SEER>=12)
AND Nz(SEER,0)<13 AND [System]="P"
AND Cooling="A", "S105")
,"Not Yet") As Measure,

I used the first IIf() to determine if the Switch() function should be
run. The Switch() function is the TruePart of the IIf() function; "Not
Yet" is the FalsePart.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVdYIechKqOuFEgEQLRtACgtOoen1XOQt9Ww84qVOxeu1M2aXUAnjPK
XOQM8GIlOyMuO91KdMomoPKb
=cyfD
-----END PGP SIGNATURE-----
I have a query make-table query that works. However, I need to add one more
if statement and when I do, along with the additional left ), I get a message
"Query too Complex..."

Is there a limit on the number of if statements Access2003 can handle?

Here is the query that works:

SELECT tg.ImportID, tg.App_ID, tg.Equipment_ID, tg.ItemID, tg.Manufacturer1,
Manufacturer2, tg.Model_No1, tg.BTUH, tg.System, tg.Product, tg.Cooling,
tg.EER, tg.SEER, tg.IPLV, tg.HighCOP, tg.LowCOP, tg.HSPF, tg.TXV, tg.ESTier,
tg.ESRebate_AmtH, tg.ESRebate_Per_Ton, ESApprove_FlagH, IIf(([Product]="AC"
Or [Product]="HP") And ([System]="S" Or [System]="P"),


IIF(([Cooling]="W" AND [EER]>=14 AND [BTUH] >=135000), "S213",

IIF(([Cooling]="W" AND [EER]>=14 AND [BTUH] <135000), "S216",

IIf([BTUH]>240000 AND [IPLV]>=10.4,"S218",

IIf(([BTUH]>=135000 And [BTUH]<=240000) AND [EER]>=10.8 AND
[IPLV]>=11.2,"S217",

IIf(([BTUH]>=65000 And [BTUH]<135000 AND [EER]>=11),"S216",

IIf(([BTUH]<65000 AND ( [EER]>=12 Or [SEER]>=14) And [TXV]=True) and
[Cooling]="A","S181",

IIf(([BTUH]<65000 AND ([EER]>=11.6 Or [SEER]>=14) And [System]="P" and
[Cooling]="A"),"S182",

IIf(([BTUH]<65000 AND (([EER]>=11.6 AND [EER]<12) Or ([SEER]>=13 AND
[SEER]<14)) And [TXV]=True),"S214",

IIf(([BTUH]<65000 AND (([EER]>=11.3 AND [EER]<11.6) Or ([SEER]>=13 AND
[SEER]<14)) And [System]="P" And [Cooling]="A"),"S215",

IIf(( [BTUH]<65000 AND [Cooling]="A" AND (([EER]>=11 AND [EER]<11.3) Or
([SEER]>=13)) And [System]="S" ),"S104",

IIf(([BTUH]<65000 AND ([EER]>=11 Or [SEER]>=12) And nz([SEER],0)<13 And
[System]="P" ) AND [Cooling]="A","S105",

"NOT YET")))))))))))) AS Measure, IIf([Measure] In ('S104','S105'),1,

IIf([Measure] In ('S213','S214','S215','S216','S217','S218'),2,

IIf([Measure] In ('S181','S182'),3,0))) AS Tier, IIf([Measure] In
('S104','S105'),Format(76,"Currency"),
IIf([Measure] In ('S214','S215'),Format(95,"Currency"),
IIf([Measure] In ('S181','S182'),Format(132,"Currency"),

IIf([Measure] In ('S213'),Format(107,"Currency"),

IIf([Measure] In ('S216'),Format(71,"Currency"),
IIf(([Measure] In ('S217','S218') And
[Cooling]="A"),Format(71,"Currency"),0)))))) AS Rebate_Per_Ton INTO
acQUALIFIEDS
FROM acSAMPLE_APPLICATION AS tg;
Click to expand...
 
D

Duane Hookom

I would wrap all of this into a user-defined function for easier, more
centralized maintenance. I am much to impatient and old to manage large
IIf() or Switch() expressions.

--
Duane Hookom
MS Access MVP


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You may want to use the Switch() function instead of the nested IIf()s:

IIf(Product IN ("AC","HP") And [System] IN ("S" "P"),
Switch(Cooling="W" AND EER>=14 AND BTUH>=135000, "S213",
Cooling="W" AND EER>=14 AND BTUH<135000, "S216",
BTUH>240000 AND IPLV>=10.4, "S218",
... etc. ... ,
BTUH<65000 AND (EER>=11 Or SEER>=12)
AND Nz(SEER,0)<13 AND [System]="P"
AND Cooling="A", "S105")
,"Not Yet") As Measure,

I used the first IIf() to determine if the Switch() function should be
run. The Switch() function is the TruePart of the IIf() function; "Not
Yet" is the FalsePart.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVdYIechKqOuFEgEQLRtACgtOoen1XOQt9Ww84qVOxeu1M2aXUAnjPK
XOQM8GIlOyMuO91KdMomoPKb
=cyfD
-----END PGP SIGNATURE-----
I have a query make-table query that works. However, I need to add one
more if statement and when I do, along with the additional left ), I get
a message "Query too Complex..."

Is there a limit on the number of if statements Access2003 can handle?

Here is the query that works:

SELECT tg.ImportID, tg.App_ID, tg.Equipment_ID, tg.ItemID,
tg.Manufacturer1, Manufacturer2, tg.Model_No1, tg.BTUH, tg.System,
tg.Product, tg.Cooling, tg.EER, tg.SEER, tg.IPLV, tg.HighCOP, tg.LowCOP,
tg.HSPF, tg.TXV, tg.ESTier, tg.ESRebate_AmtH, tg.ESRebate_Per_Ton,
ESApprove_FlagH, IIf(([Product]="AC" Or [Product]="HP") And ([System]="S"
Or [System]="P"),


IIF(([Cooling]="W" AND [EER]>=14 AND [BTUH] >=135000), "S213",

IIF(([Cooling]="W" AND [EER]>=14 AND [BTUH] <135000), "S216",

IIf([BTUH]>240000 AND [IPLV]>=10.4,"S218",

IIf(([BTUH]>=135000 And [BTUH]<=240000) AND [EER]>=10.8 AND
[IPLV]>=11.2,"S217",

IIf(([BTUH]>=65000 And [BTUH]<135000 AND [EER]>=11),"S216",

IIf(([BTUH]<65000 AND ( [EER]>=12 Or [SEER]>=14) And [TXV]=True) and
[Cooling]="A","S181",

IIf(([BTUH]<65000 AND ([EER]>=11.6 Or [SEER]>=14) And [System]="P" and
[Cooling]="A"),"S182",

IIf(([BTUH]<65000 AND (([EER]>=11.6 AND [EER]<12) Or ([SEER]>=13 AND
[SEER]<14)) And [TXV]=True),"S214",

IIf(([BTUH]<65000 AND (([EER]>=11.3 AND [EER]<11.6) Or ([SEER]>=13 AND
[SEER]<14)) And [System]="P" And [Cooling]="A"),"S215",

IIf(( [BTUH]<65000 AND [Cooling]="A" AND (([EER]>=11 AND [EER]<11.3) Or
([SEER]>=13)) And [System]="S" ),"S104",

IIf(([BTUH]<65000 AND ([EER]>=11 Or [SEER]>=12) And nz([SEER],0)<13 And
[System]="P" ) AND [Cooling]="A","S105",

"NOT YET")))))))))))) AS Measure, IIf([Measure] In ('S104','S105'),1,

IIf([Measure] In ('S213','S214','S215','S216','S217','S218'),2,

IIf([Measure] In ('S181','S182'),3,0))) AS Tier, IIf([Measure] In
('S104','S105'),Format(76,"Currency"),
IIf([Measure] In ('S214','S215'),Format(95,"Currency"),
IIf([Measure] In ('S181','S182'),Format(132,"Currency"),

IIf([Measure] In ('S213'),Format(107,"Currency"),

IIf([Measure] In ('S216'),Format(71,"Currency"),
IIf(([Measure] In ('S217','S218') And
[Cooling]="A"),Format(71,"Currency"),0)))))) AS Rebate_Per_Ton INTO
acQUALIFIEDS
FROM acSAMPLE_APPLICATION AS tg;
Click to expand...
Click to expand...
 
M

MGFoster

Duane said:
I would wrap all of this into a user-defined function for easier, more
centralized maintenance. I am much to impatient and old to manage large
IIf() or Switch() expressions.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, I believe the best answer is to put all the comparison data
into a table, or tables, and just join that big table to the comparison
data table(s) to get the "S" numbers. I didn't say that before, 'cuz I
don't know anything about the db's design/purpose, and didn't want to
design a db for the OP.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaDvIechKqOuFEgEQKizgCfdLYEzSOcdf1M04G9smsEFH5tF6MAoKaq
4vJXi2p7gOx4ll2yS0N4PMPO
=x7EF
-----END PGP SIGNATURE-----
 
D

Duane Hookom

Good point, I agree that modeling business rules in tables is the preferred
method.
 
G

Guest

I'm not sure how you'd do that (i.e. JOIN the comparison data to the table
for which you're trying to return the "S" data, when you have ranges
involved. For example, let's say the Lookup table has a range of 65000 to
100000 btu to qualify (return an "S"), and the table being evaluated contains
units of various btu's. How do you join something like that?
 
J

John Vinson

I'm not sure how you'd do that (i.e. JOIN the comparison data to the table
for which you're trying to return the "S" data, when you have ranges
involved. For example, let's say the Lookup table has a range of 65000 to
100000 btu to qualify (return an "S"), and the table being evaluated contains
units of various btu's. How do you join something like that?
Click to expand...

A "Non Equi Join" is pretty slick at this. Make your Lookup table with
three fields - LowBTU, HiBTU, ResultCode. Join it to your table with a
JOIN clause like

INNER JOIN ResultCodes
ON BTU >= ResultCodes.LowBTU AND BTU < ResultCodes.HiBTU


John W. Vinson[MVP]
 
G

Guest

Thank you, John. Very much appreciated.

John Vinson said:
A "Non Equi Join" is pretty slick at this. Make your Lookup table with
three fields - LowBTU, HiBTU, ResultCode. Join it to your table with a
JOIN clause like

INNER JOIN ResultCodes
ON BTU >= ResultCodes.LowBTU AND BTU < ResultCodes.HiBTU


John W. Vinson[MVP]
Click to expand...
 
Top