Complex Switch Function or Maybe it should be IIF

L

LisaInAz

Hi - I have read all the "complex IIF" statement and research the Switch
Function.
I have a table that contains Instructors, various Dollar Amount to Charge
{AMT1...BAMT4}, based on an age grouping, based on whether it is for a Full
Day or Half a day. The Data on the table is as ADay AAmt1 AAmt2
AAmt3 AAmt4 or BDay BAmt0 BAmt1 BAmt3 BAmt4. The Full or Part day data
could be in either "set" of fields

Based on my clients Age I want to "display" the Amt Charged for the Day
criteria

Example: 3-5 6-9 10-12 13-18
FULL DAY (D) 23.60 21.20 25.90 35.00
PART DAY(L) 8.65 9.80 10.15 15.00

I have had some success with SWITCH FUNCTION in a Query


Switch([ChildAge]>0,[AAMT1],[ChildAge]>5,[AAMT2],[ChildAge]>9,[AAMT3],[ChildAge]>12,[AAMT4],[ChildAge]>18,99)

But when I try to add in the DAY so I can insure I am getting the correct
field the Display Field Shows #Error

Switch([ADAY]="L" And [ChildAge]>0,[AAMT1],[ADAY]="L" And
[ChildAge]>5,[AAMT2],[ADAY]="L" And [ChildAge]>9,[AAMT3],[ADAY]="L" And
[ChildAge]>12,[AAMT4],[ChildAge]>18,[BDAY]="L" And
[ChildAge]>0,[BAMT1],[BDAY]="L" And [ChildAge]>5,[BAMT2],[KND002]="L" And
[ChildAge]>9,[BDAY3],[KND002]="L" And [ChildAge]>12,[BDAY4],[ChildAge]>18,99)

I also should share that any of the Amount Charge fields could be Null if
the instructor doesn't teach that age group

I am not very strong in coding. I tried an IIF statement and totally bombed
it.
Any suggestions or other ways would be so very helpful as I am under a time
crunch and struggling.
Thank you in Advance.
 
A

Allen Browne

The best way to to do this would be to use a table for the prices rather
than nested IIf() or Switch().

Your table would have fields like this:
RateType Text
MinAge Number
Charge Currency
So its records would look like this:
D 3 23.60
D 6 21.20
D 10 25.90
D 13 35.00
L 3 8.65
L 6 9.80
Now you can use this table to get the price.

You will still have questions about how to get the price for the age range.
Tom Ellision explains how in this article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

If you find that approach too involved, just put a record in for each age,
so you don't need to worry about how to figure out what range you're in.
 
L

LisaInAz

Thanks -
What I ended up doing is populating my table where the data i am looking at
is in the specific field and I am able to use the Switch() very nicely.

Allen Browne said:
The best way to to do this would be to use a table for the prices rather
than nested IIf() or Switch().

Your table would have fields like this:
RateType Text
MinAge Number
Charge Currency
So its records would look like this:
D 3 23.60
D 6 21.20
D 10 25.90
D 13 35.00
L 3 8.65
L 6 9.80
Now you can use this table to get the price.

You will still have questions about how to get the price for the age range.
Tom Ellision explains how in this article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

If you find that approach too involved, just put a record in for each age,
so you don't need to worry about how to figure out what range you're in.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


LisaInAz said:
Hi - I have read all the "complex IIF" statement and research the Switch
Function.
I have a table that contains Instructors, various Dollar Amount to Charge
{AMT1...BAMT4}, based on an age grouping, based on whether it is for a
Full
Day or Half a day. The Data on the table is as ADay AAmt1 AAmt2
AAmt3 AAmt4 or BDay BAmt0 BAmt1 BAmt3 BAmt4. The Full or Part day data
could be in either "set" of fields

Based on my clients Age I want to "display" the Amt Charged for the Day
criteria

Example: 3-5 6-9 10-12 13-18
FULL DAY (D) 23.60 21.20 25.90 35.00
PART DAY(L) 8.65 9.80 10.15 15.00

I have had some success with SWITCH FUNCTION in a Query


Switch([ChildAge]>0,[AAMT1],[ChildAge]>5,[AAMT2],[ChildAge]>9,[AAMT3],[ChildAge]>12,[AAMT4],[ChildAge]>18,99)

But when I try to add in the DAY so I can insure I am getting the correct
field the Display Field Shows #Error

Switch([ADAY]="L" And [ChildAge]>0,[AAMT1],[ADAY]="L" And
[ChildAge]>5,[AAMT2],[ADAY]="L" And [ChildAge]>9,[AAMT3],[ADAY]="L" And
[ChildAge]>12,[AAMT4],[ChildAge]>18,[BDAY]="L" And
[ChildAge]>0,[BAMT1],[BDAY]="L" And [ChildAge]>5,[BAMT2],[KND002]="L" And
[ChildAge]>9,[BDAY3],[KND002]="L" And
[ChildAge]>12,[BDAY4],[ChildAge]>18,99)

I also should share that any of the Amount Charge fields could be Null if
the instructor doesn't teach that age group

I am not very strong in coding. I tried an IIF statement and totally
bombed
it.
Any suggestions or other ways would be so very helpful as I am under a
time
crunch and struggling.
Thank you in Advance.
Click to expand...
Click to expand...
 
Top