I have an excel formula that I used a VLookup to calculate the Fiscal Year(FY) of a signed document. I changed it to an IIF statement for Access but the Query won't accept it as an expression because of it's size. Is there another way to accomplish this?
Last_FSA_FY: IIF([Last_Signature_Date]Between10/01/00#and#09/30/01#,"FY01",IIF([Last_Signature_Date] Between #10/01/01#and#09/30/02#, "FY02", IF([Last_Signature_Date]Between10/01/02#and#09/30/03#,"FY03",IIF([Last_Signature_Date] Between #10/01/03#and#09/30/04#, "FY04", IIF([Last_Signature_Date]Between10/01/04#and#09/30/05#,"FY05",IIF([Last_Signature_Date] Between #10/01/05#and#09/30/06#, "FY06", IIF([Last_Signature_Date]Between10/01/06#and#09/30/07#,"FY07",IIF([Last_Signature_Date] Between #10/01/07#and#09/30/08#, "FY08", IIF([Last_Signature_Date]Between10/01/08#and#09/30/09#,"FY09",IIF([Last_Signature_Date] Between #10/01/09#and#09/30/10#, "FY10", IIF([Last_Signature_Date]Between10/01/10#and#09/30/11#,"FY11",IIF([Last_Signature_Date] Between #10/01/11#and#09/30/12#, "FY12", IIF([Last_Signature_Date]Between10/01/12#and#09/30/13#,"FY13",IIF([Last_Signature_Date] Between #10/01/13#and#09/30/14#, "FY14", IIF([Last_Signature_Date]Between10/01/14#and#09/30/15#,"FY15",IIF([Last_Signature_Date] Between #10/01/15#and#09/30/16#, "FY16","No FSA"))