Excel VLookup vs Access IIF statement

[Huddle]

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"))

[pbaldy]

Personally I'd probably have a table with fiscal year, begin and end dates. That would make it fairly easy to get from anywhere in the app.

If you want to stick with that, you can use the Switch() function which would be simpler. You certainly don't have enough ending parentheses if that's the actual function. Using either function, you don't need to test both sides of the range; you use the fact that Access will stop looking after the first True result.

[Huddle]

A table would work fine. So I would have the start date, end date and FY. How do you do a look up in a calculation?

[pbaldy]

In a query like you're trying to get it above, a non-equi join is the most efficient:

SELECT...
FROM MainTable LEFT JOIN FiscalYears ON MainTable.[Last_Signature_Date] >= FiscalYear.StartDate AND MainTable.[Last_Signature_Date] <= FiscalYear.EndDate

Note the non-equi join can't be represented in the query design grid.

[Huddle]

I've never work in SQL before. Is the code entered at the end of the SQL that is already in there or is it inserted in the middle.

I've attached a screen shot for your review. The Query I'm working with is Q_Building_Information. The Table is Lookup_Tbl_Fiscal_Year.

Are there Relationship issues I need to be aware of?

Last_FSA_FY:1, Next_FSA:1, Next_FSA_FY:1 in the Q are just place holders until I figure out how to make this work.

[pbaldy]

It would add onto the SQL at the end (replacing your FROM clause).

[Huddle]

I got the code right I believe but can't figure out where the data shows. How is it connected to a individual field?

SELECT Tbl_Building_Information.Bldg_Number, Tbl_Building_Information.FSL, Tbl_Building_Information.Last_FSA, Tbl_Building_Information.Last_Signature_Date, Tbl_Building_Information.Bldg_Name, Tbl_Building_Information.Address, Tbl_Building_Information.City, Tbl_Building_Information.State, Tbl_Building_Information.Zip, Tbl_Building_Information.Commander, Tbl_Building_Information.Inspector_First_Name, Tbl_Building_Information.Inspector_Last_Name, Tbl_Building_Information.On_Schedule, Tbl_Building_Information.District, Tbl_Building_Information.Delegated, Tbl_Building_Information.Population, Tbl_Building_Information.Owned_Leased, Tbl_Building_Information.Lease_Exp_Date, Tbl_Building_Information.Sq_Feet, Tbl_Building_Information.GSA_Contact, Tbl_Building_Information.Notes, Tbl_Building_Information.In_Ramp, Tbl_Building_Information.In_EIS, Tbl_Building_Information.FPS_Responsible, Tbl_Building_Information.Review_Date
FROM Tbl_Building_Information LEFT JOIN Lookup_Tbl_Fiscal_Year ON Tbl_Building_Information.[Last_Signature_Date]>=Lookup_Tbl_Fiscal_Year.Start_Date AND Tbl_Building_Information.[Last_Signature_Date]<=Lookup_Tbl_Fiscal_Year.End_Date

[pbaldy]

You'd add the field from the fiscal year table. Try

SELECT Tbl_Building_Information.Bldg_Number, Tbl_Building_Information.FSL, Tbl_Building_Information.Last_FSA, Tbl_Building_Information.Last_Signature_Date, Tbl_Building_Information.Bldg_Name, Tbl_Building_Information.Address, Tbl_Building_Information.City, Tbl_Building_Information.State, Tbl_Building_Information.Zip, Tbl_Building_Information.Commander, Tbl_Building_Information.Inspector_First_Name, Tbl_Building_Information.Inspector_Last_Name, Tbl_Building_Information.On_Schedule, Tbl_Building_Information.District, Tbl_Building_Information.Delegated, Tbl_Building_Information.Population, Tbl_Building_Information.Owned_Leased, Tbl_Building_Information.Lease_Exp_Date, Tbl_Building_Information.Sq_Feet, Tbl_Building_Information.GSA_Contact, Tbl_Building_Information.Notes, Tbl_Building_Information.In_Ramp, Tbl_Building_Information.In_EIS, Tbl_Building_Information.FPS_Responsible, Tbl_Building_Information.Review_Date, Lookup_Tbl_Fiscal_Year.FY
FROM Tbl_Building_Information LEFT JOIN Lookup_Tbl_Fiscal_Year ON Tbl_Building_Information.[Last_Signature_Date]>=Lookup_Tbl_Fiscal_Year.Start_Date AND Tbl_Building_Information.[Last_Signature_Date]<=Lookup_Tbl_Fiscal_Year.End_Date

[Huddle]

It Works!! By the way I know who John Galt is...a very smart man. Thanks for your help.

[pbaldy]

Happy to help, glad it worked for you.