Hello...
I'm working with access and there are two expressions that I can't figure out a better way to make. These work and the syntax is correct, but there must be a more streamlined way of doing it.
JJ
SleevesRequired: IIf([NumberCDs]<=12,0,IIf([NumberCDs]=13,7,IIf([NumberCDs]=15,8,IIf([NumberCDs]=17,9,IIf([NumberCDs]=19,10,IIf([NumberCDs]=21,11,IIf([NumberCDs]=23,12,Round([NumberCDs]/2))))))))
What I'm looking for is for any number of cd's 12 or less, return 0. Any number of cd's 13 or more should be the number of cd's, rounded to the next even number (13 should be 14) and then divided by 2 (13 and 14 cd's would require 7 sleeves).
CaseCost: IIf([NumberCDs]=2,0.15,IIf([NumberCDs]=3,0.29,IIf([NumberCDs]=4,0.29,IIf([NumberCDs]=5,0.49,IIf([NumberCDs]=6,0.49,IIf([NumberCDs]=7,0.62,IIf([NumberCDs]=8,0.62,IIf([NumberCDs]=9,0.73,IIf([NumberCDs]=10,0.73,IIf([NumberCDs]=11,1.03,IIf([NumberCDs]=12,1.03,2.2)))))))))))
This is showing the price per case. 5 and 6 cd's will go into a 6 case and 7 and 8 cd's will go into an 8 case. They should be groupable where "IIf([NumberCDs]=3 or 4,0.29" type of thing.
I'm working with access and there are two expressions that I can't figure out a better way to make. These work and the syntax is correct, but there must be a more streamlined way of doing it.
JJ
SleevesRequired: IIf([NumberCDs]<=12,0,IIf([NumberCDs]=13,7,IIf([NumberCDs]=15,8,IIf([NumberCDs]=17,9,IIf([NumberCDs]=19,10,IIf([NumberCDs]=21,11,IIf([NumberCDs]=23,12,Round([NumberCDs]/2))))))))
What I'm looking for is for any number of cd's 12 or less, return 0. Any number of cd's 13 or more should be the number of cd's, rounded to the next even number (13 should be 14) and then divided by 2 (13 and 14 cd's would require 7 sleeves).
CaseCost: IIf([NumberCDs]=2,0.15,IIf([NumberCDs]=3,0.29,IIf([NumberCDs]=4,0.29,IIf([NumberCDs]=5,0.49,IIf([NumberCDs]=6,0.49,IIf([NumberCDs]=7,0.62,IIf([NumberCDs]=8,0.62,IIf([NumberCDs]=9,0.73,IIf([NumberCDs]=10,0.73,IIf([NumberCDs]=11,1.03,IIf([NumberCDs]=12,1.03,2.2)))))))))))
This is showing the price per case. 5 and 6 cd's will go into a 6 case and 7 and 8 cd's will go into an 8 case. They should be groupable where "IIf([NumberCDs]=3 or 4,0.29" type of thing.
