Need IIF statement to alternate end dates by Billing Frequency

  • Thread starter Thread starter EC
  • Start date Start date
E

EC

I have a query on subscriptions where I need to alternate
the end date of the sub_Subscr_End_Date by Billing
Frequency data. Example, if M (monthly) I need to
alternate the sub_Subscr_End_Date backwards one month and
if quarterly, backwards three months and if annual,
backwards 12 months. This is what I have started with
expression wise and here are my results.


Expr1: (IIf([sub_Billing_Frequency]="Q",
[sub_Subscr_End_Date]-"m",3))

I get values like,
sub_Last_Order_ID Sub_Billing_Frequency
sub_Subscr_End_Date Expr1
1 M 1/13/2004 1/2/1900
2B Q 1/13/2004 #Error
1A Q 1/13/2004 #Error
6 Q 1/13/2004 #Error
3 M 1/13/2004 1/2/1900
0 Q 1/13/2004 #Error
Q6 Q 1/13/2004 #Error
T1 Q 1/13/2004 #Error
IP M 1/14/2004 1/2/1900

Any help is greatly appreciated. I am very new to this
and trying hard to learn on my own but it becomes both
frustrating and very time consuming.

Thanks in advance!!!!!!!
 
You need to build a nested IIF statement. If you have 3 "Yes" choices,
embed another IIF statement in the "No" choice of the preceding level. If
possible shorten your field names.

Pseudocode:

IIF(A,Criteria1="a",IIF(B,Criteria2="b",Criteria3="c"))

Do it a piece at a time, add complexity, test, repeat.
 
Understood-thanks. However, I guess my initial problem
is why am I getting the date return on the month's like I
am. I understand the error because I did not enter the
nested part for q, etc...)

Any additional direction is appreciated.

Thank you for the valuable input and your time!
 
Back
Top