expression to complex - switch & iff in Access 2007

[xz]

My Switch is getting too complex - 14+ cases - and I have a couple
more cases to enter.
But it explodes if I try to add more. I can't use a lookup table
because my expressions don't lend to that - because they depend on
both TCODE and SACCTNUM. Anyone know how to overcome this? E.g.,

SELECT Switch(
[TCODE]="4030","Purchase - Calc harm if remed eligible" ,
[TCODE]="3021","Dividend - Calc harm if associated w/ remed purch",
[TCODE]="8081","FIFO Sale - Subtract CDSC: "+Str([TBL1.CDSC]),
[TCODE]="7011" And IsNull([TBL1.SACCTNUM])<>True,"Not purchase",
[TCODE]="7011" And IsNull([TBL1.SACCTNUM])=True,"Purchase",
[TCODE]="7512" And IsNull([TBL1.SACCTNUM])<>True,"No Sale ",
[TCODE]="7512" And IsNull([TBL1.SACCTNUM])=True,"FIFO Sale",
[TCODE]="6011" And IsNull([TBL1.SACCTNUM])<>True,"Not purchase",
[TCODE]="6011" And IsNull([TBL1.SACCTNUM])=True,"Purch",
[TCODE]="6512" And IsNull([TBL1.SACCTNUM])<>True,"No sale",
[TCODE]="6512" And IsNull([TBL1.SACCTNUM])=True,"FIFO sale",
[TCODE]="7551","FIFO Sale - unmatched xchange",
[TCODE]= "6600","FIFO Sale by netwk conv",
[TCODE]="1010","FIFO pos adjust")
FROM TBL1;

And if someone says create a custom function - I'd love to, but not
sure how to do that in 2007... Although I use Access & .NET a lot, I'm
not as familiar VBA so not sure what to do. I sure wish Access was
more robust and could handle this.

Jason Shohet

 

[Michel Walsh]

Using a lookup table is still preferable, and doable:

Code IsNull Desc ' fields name
4030 false "Purchase ...."
4030 true "Purchase ... "
3021 false "Dividend ..."
3021 true "Dividend ... "
....
7011 false "Not purchase"
7011 true "Purchase"
....


and then you make the lookup on the two fields:



SELECT ..., b.Desc
FROM myTable AS a INNER JOIN lookupTable AS b
ON a.code=b.code AND IsNull(TBL1.SACCTNUM) = b.IsNull



Note: [TBL1.SACCTNUM] is probably an error, is it not [TBL1].[SACCTNUM]
or simply TBL1.SACCTNUM ?


The limitation on the number of arguments for switch is a VBA limitation,
not a JET limitation. Jet just calls VBA, at that point.



Hoping it may help,
Vanderghast, Access MVP

 

[xz]

makes perfect sense Vanderghast.
Sometimes the obvious eludes us