If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IIF statement for Last Fiscal Year
I have a query that needs to return Last Fiscal Year's
information to me. I already have one query that gives me the Current Fiscal Year, but I'm getting stuck on the Last Fiscal Year. Our Fiscal Year runs from July to June 30. Since I already have the data out there for the entire last fiscal year, that's where I'm getting confused on the IIF statement. Here's an example of my table Item Whse Month Year DownloadDate MonthDesc RegSale RegGrsSale PromoSale PromoGrsSale 100005 C 1 2004 2/6/04 2:28:02 PM Jan $158.00 $35.00 $0.00 $0.00 100005 C 2 2004 2/27/04 1:28:03 AM Feb $158.00 $34.00 $0.00 $0.00 100005 C 3 2004 3/23/04 1:28:02 AM Mar $79.00 $17.00 $0.00 $0.00 100005 C 4 2004 4/28/04 3:28:02 AM Apr $661.00 $141.00 $0.00 $0.00 100005 C 5 2004 5/14/04 2:28:07 AM May $500.00 $110.00 $0.00 $0.00 100005 C 7 2003 9/29/03 11:28:02 AM Jul $945.00 $208.00 $0.00 $0.00 100005 C 8 2003 9/29/03 10:28:01 AM Aug $473.00 $104.00 $0.00 $0.00 100005 C 9 2003 9/30/03 3:28:01 AM Sep $709.00 $156.00 $0.00 $0.00 100005 C 10 2003 10/28/03 2:28:02 AM Oct $711.00 $155.00 $0.00 $0.00 100005 C 11 2003 11/25/03 2:28:02 AM Nov $237.00 $51.00 $0.00 $0.00 100005 C 12 2003 2/6/04 1:40:04 PM Dec $389.00 $81.00 $0.00 $0.00 I need to add up all of the sales information for last fiscal year which would be July 2002 - June 2003 right now. This portion of the table doesn't have that data, but it can be changed. I need to have this running by the end of the month and I have other pieces to finish. I'm horrible at IIF statements, so if anyone could help me, I would greatly appreciate it! Thanks so much! Liz |
#2
|
|||
|
|||
IIF statement for Last Fiscal Year
Liz,
What are you using to determine current fiscal year data? Is it the DownloadDate or are you looking at month and year? Since you already have a query working for current fiscal year, it seems the easiest thing would be to make a new query based on this one, and just change the criteria. -----Original Message----- I have a query that needs to return Last Fiscal Year's information to me. I already have one query that gives me the Current Fiscal Year, but I'm getting stuck on the Last Fiscal Year. Our Fiscal Year runs from July to June 30. Since I already have the data out there for the entire last fiscal year, that's where I'm getting confused on the IIF statement. Here's an example of my table Item Whse Month Year DownloadDate MonthDesc RegSale RegGrsSale PromoSale PromoGrsSale 100005 C 1 2004 2/6/04 2:28:02 PM Jan $158.00 $35.00 $0.00 $0.00 100005 C 2 2004 2/27/04 1:28:03 AM Feb $158.00 $34.00 $0.00 $0.00 100005 C 3 2004 3/23/04 1:28:02 AM Mar $79.00 $17.00 $0.00 $0.00 100005 C 4 2004 4/28/04 3:28:02 AM Apr $661.00 $141.00 $0.00 $0.00 100005 C 5 2004 5/14/04 2:28:07 AM May $500.00 $110.00 $0.00 $0.00 100005 C 7 2003 9/29/03 11:28:02 AM Jul $945.00 $208.00 $0.00 $0.00 100005 C 8 2003 9/29/03 10:28:01 AM Aug $473.00 $104.00 $0.00 $0.00 100005 C 9 2003 9/30/03 3:28:01 AM Sep $709.00 $156.00 $0.00 $0.00 100005 C 10 2003 10/28/03 2:28:02 AM Oct $711.00 $155.00 $0.00 $0.00 100005 C 11 2003 11/25/03 2:28:02 AM Nov $237.00 $51.00 $0.00 $0.00 100005 C 12 2003 2/6/04 1:40:04 PM Dec $389.00 $81.00 $0.00 $0.00 I need to add up all of the sales information for last fiscal year which would be July 2002 - June 2003 right now. This portion of the table doesn't have that data, but it can be changed. I need to have this running by the end of the month and I have other pieces to finish. I'm horrible at IIF statements, so if anyone could help me, I would greatly appreciate it! Thanks so much! Liz . |
#3
|
|||
|
|||
IIF statement for Last Fiscal Year
That's my problem though. My query is taking up to the
current, for last fiscal year (which, yes, I am using month and year) I can just take everything that is there because that would be the whole year. I need Last Fiscal Year to Date. So, if this is October, I need Last Fiscal up to October. The current fiscal year is easier because I don't have a cut off because there is no data past October. I would have to say something like if month = 10, then add month 7+8+9+10 from last fiscal year. Does that make sense? Thanks, Liz -----Original Message----- Liz, What are you using to determine current fiscal year data? Is it the DownloadDate or are you looking at month and year? Since you already have a query working for current fiscal year, it seems the easiest thing would be to make a new query based on this one, and just change the criteria. -----Original Message----- I have a query that needs to return Last Fiscal Year's information to me. I already have one query that gives me the Current Fiscal Year, but I'm getting stuck on the Last Fiscal Year. Our Fiscal Year runs from July to June 30. Since I already have the data out there for the entire last fiscal year, that's where I'm getting confused on the IIF statement. Here's an example of my table Item Whse Month Year DownloadDate MonthDesc RegSale RegGrsSale PromoSale PromoGrsSale 100005 C 1 2004 2/6/04 2:28:02 PM Jan $158.00 $35.00 $0.00 $0.00 100005 C 2 2004 2/27/04 1:28:03 AM Feb $158.00 $34.00 $0.00 $0.00 100005 C 3 2004 3/23/04 1:28:02 AM Mar $79.00 $17.00 $0.00 $0.00 100005 C 4 2004 4/28/04 3:28:02 AM Apr $661.00 $141.00 $0.00 $0.00 100005 C 5 2004 5/14/04 2:28:07 AM May $500.00 $110.00 $0.00 $0.00 100005 C 7 2003 9/29/03 11:28:02 AM Jul $945.00 $208.00 $0.00 $0.00 100005 C 8 2003 9/29/03 10:28:01 AM Aug $473.00 $104.00 $0.00 $0.00 100005 C 9 2003 9/30/03 3:28:01 AM Sep $709.00 $156.00 $0.00 $0.00 100005 C 10 2003 10/28/03 2:28:02 AM Oct $711.00 $155.00 $0.00 $0.00 100005 C 11 2003 11/25/03 2:28:02 AM Nov $237.00 $51.00 $0.00 $0.00 100005 C 12 2003 2/6/04 1:40:04 PM Dec $389.00 $81.00 $0.00 $0.00 I need to add up all of the sales information for last fiscal year which would be July 2002 - June 2003 right now. This portion of the table doesn't have that data, but it can be changed. I need to have this running by the end of the month and I have other pieces to finish. I'm horrible at IIF statements, so if anyone could help me, I would greatly appreciate it! Thanks so much! Liz . . |
#4
|
|||
|
|||
IIF statement for Last Fiscal Year
I think you could do something like this in criteria:
fields month year criteria 6 2003 lines 7 2003 This would give you everything between 7/2003 and 6/2004 Or you could create a field in a query like: Selectit:IIF((year = 2003 and month 6) or (year 2003 and month 7),"Yes","No") set criteria to "Yes" - meaning you want all those that satisfy the above criteria. Hope this helps -----Original Message----- That's my problem though. My query is taking up to the current, for last fiscal year (which, yes, I am using month and year) I can just take everything that is there because that would be the whole year. I need Last Fiscal Year to Date. So, if this is October, I need Last Fiscal up to October. The current fiscal year is easier because I don't have a cut off because there is no data past October. I would have to say something like if month = 10, then add month 7+8+9+10 from last fiscal year. Does that make sense? Thanks, Liz -----Original Message----- Liz, What are you using to determine current fiscal year data? Is it the DownloadDate or are you looking at month and year? Since you already have a query working for current fiscal year, it seems the easiest thing would be to make a new query based on this one, and just change the criteria. -----Original Message----- I have a query that needs to return Last Fiscal Year's information to me. I already have one query that gives me the Current Fiscal Year, but I'm getting stuck on the Last Fiscal Year. Our Fiscal Year runs from July to June 30. Since I already have the data out there for the entire last fiscal year, that's where I'm getting confused on the IIF statement. Here's an example of my table Item Whse Month Year DownloadDate MonthDesc RegSale RegGrsSale PromoSale PromoGrsSale 100005 C 1 2004 2/6/04 2:28:02 PM Jan $158.00 $35.00 $0.00 $0.00 100005 C 2 2004 2/27/04 1:28:03 AM Feb $158.00 $34.00 $0.00 $0.00 100005 C 3 2004 3/23/04 1:28:02 AM Mar $79.00 $17.00 $0.00 $0.00 100005 C 4 2004 4/28/04 3:28:02 AM Apr $661.00 $141.00 $0.00 $0.00 100005 C 5 2004 5/14/04 2:28:07 AM May $500.00 $110.00 $0.00 $0.00 100005 C 7 2003 9/29/03 11:28:02 AM Jul $945.00 $208.00 $0.00 $0.00 100005 C 8 2003 9/29/03 10:28:01 AM Aug $473.00 $104.00 $0.00 $0.00 100005 C 9 2003 9/30/03 3:28:01 AM Sep $709.00 $156.00 $0.00 $0.00 100005 C 10 2003 10/28/03 2:28:02 AM Oct $711.00 $155.00 $0.00 $0.00 100005 C 11 2003 11/25/03 2:28:02 AM Nov $237.00 $51.00 $0.00 $0.00 100005 C 12 2003 2/6/04 1:40:04 PM Dec $389.00 $81.00 $0.00 $0.00 I need to add up all of the sales information for last fiscal year which would be July 2002 - June 2003 right now. This portion of the table doesn't have that data, but it can be changed. I need to have this running by the end of the month and I have other pieces to finish. I'm horrible at IIF statements, so if anyone could help me, I would greatly appreciate it! Thanks so much! Liz . . . |
#5
|
|||
|
|||
IIF statement for Last Fiscal Year
I see what you are trying to see:
How about a few Functions? Paste these into a standard module: Public Function FiscalYear(Optional ByVal dteDate As Date = 0) As Integer If dteDate = 0 Then dteDate = Date FiscalYear = Year(DateAdd("m", 6, dteDate)) End Function Public Function FirstDateOfFY(Optional ByVal intFY As Integer = 0) As Date Dim dteFirst As Date If intFY = 0 Then intFY = FiscalYear FirstDateOfFY = DateSerial(intFY, 7, 1) End Function Now, your clause for the date field can be: Between FirstDateOfFY(FiscalYear()-1) And DateAdd("yyyy",- 1,Date()) Chris Nebinger -----Original Message----- That's my problem though. My query is taking up to the current, for last fiscal year (which, yes, I am using month and year) I can just take everything that is there because that would be the whole year. I need Last Fiscal Year to Date. So, if this is October, I need Last Fiscal up to October. The current fiscal year is easier because I don't have a cut off because there is no data past October. I would have to say something like if month = 10, then add month 7+8+9+10 from last fiscal year. Does that make sense? Thanks, Liz -----Original Message----- Liz, What are you using to determine current fiscal year data? Is it the DownloadDate or are you looking at month and year? Since you already have a query working for current fiscal year, it seems the easiest thing would be to make a new query based on this one, and just change the criteria. -----Original Message----- I have a query that needs to return Last Fiscal Year's information to me. I already have one query that gives me the Current Fiscal Year, but I'm getting stuck on the Last Fiscal Year. Our Fiscal Year runs from July to June 30. Since I already have the data out there for the entire last fiscal year, that's where I'm getting confused on the IIF statement. Here's an example of my table Item Whse Month Year DownloadDate MonthDesc RegSale RegGrsSale PromoSale PromoGrsSale 100005 C 1 2004 2/6/04 2:28:02 PM Jan $158.00 $35.00 $0.00 $0.00 100005 C 2 2004 2/27/04 1:28:03 AM Feb $158.00 $34.00 $0.00 $0.00 100005 C 3 2004 3/23/04 1:28:02 AM Mar $79.00 $17.00 $0.00 $0.00 100005 C 4 2004 4/28/04 3:28:02 AM Apr $661.00 $141.00 $0.00 $0.00 100005 C 5 2004 5/14/04 2:28:07 AM May $500.00 $110.00 $0.00 $0.00 100005 C 7 2003 9/29/03 11:28:02 AM Jul $945.00 $208.00 $0.00 $0.00 100005 C 8 2003 9/29/03 10:28:01 AM Aug $473.00 $104.00 $0.00 $0.00 100005 C 9 2003 9/30/03 3:28:01 AM Sep $709.00 $156.00 $0.00 $0.00 100005 C 10 2003 10/28/03 2:28:02 AM Oct $711.00 $155.00 $0.00 $0.00 100005 C 11 2003 11/25/03 2:28:02 AM Nov $237.00 $51.00 $0.00 $0.00 100005 C 12 2003 2/6/04 1:40:04 PM Dec $389.00 $81.00 $0.00 $0.00 I need to add up all of the sales information for last fiscal year which would be July 2002 - June 2003 right now. This portion of the table doesn't have that data, but it can be changed. I need to have this running by the end of the month and I have other pieces to finish. I'm horrible at IIF statements, so if anyone could help me, I would greatly appreciate it! Thanks so much! Liz . . . |
#6
|
|||
|
|||
IIF statement for Last Fiscal Year
Liz,
Primarily, your problem comes from treating an Access table like an Excel spreadsheet. You can do what you want to do much easier if you will redesign your table as follows: SalesTable Item Whse SaleDate SaleAmount SaleType Then you could run a query similar to the following: Select Item, Whse, Sum(SaleAmount) From SalesTable Where SaleDate Between #BeginningDate# And #EndingDate# (Replace words with dates) Group By SaleType, Item, Whse; -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Liz" wrote in message ... I have a query that needs to return Last Fiscal Year's information to me. I already have one query that gives me the Current Fiscal Year, but I'm getting stuck on the Last Fiscal Year. Our Fiscal Year runs from July to June 30. Since I already have the data out there for the entire last fiscal year, that's where I'm getting confused on the IIF statement. Here's an example of my table Item Whse Month Year DownloadDate MonthDesc RegSale RegGrsSale PromoSale PromoGrsSale 100005 C 1 2004 2/6/04 2:28:02 PM Jan $158.00 $35.00 $0.00 $0.00 100005 C 2 2004 2/27/04 1:28:03 AM Feb $158.00 $34.00 $0.00 $0.00 100005 C 3 2004 3/23/04 1:28:02 AM Mar $79.00 $17.00 $0.00 $0.00 100005 C 4 2004 4/28/04 3:28:02 AM Apr $661.00 $141.00 $0.00 $0.00 100005 C 5 2004 5/14/04 2:28:07 AM May $500.00 $110.00 $0.00 $0.00 100005 C 7 2003 9/29/03 11:28:02 AM Jul $945.00 $208.00 $0.00 $0.00 100005 C 8 2003 9/29/03 10:28:01 AM Aug $473.00 $104.00 $0.00 $0.00 100005 C 9 2003 9/30/03 3:28:01 AM Sep $709.00 $156.00 $0.00 $0.00 100005 C 10 2003 10/28/03 2:28:02 AM Oct $711.00 $155.00 $0.00 $0.00 100005 C 11 2003 11/25/03 2:28:02 AM Nov $237.00 $51.00 $0.00 $0.00 100005 C 12 2003 2/6/04 1:40:04 PM Dec $389.00 $81.00 $0.00 $0.00 I need to add up all of the sales information for last fiscal year which would be July 2002 - June 2003 right now. This portion of the table doesn't have that data, but it can be changed. I need to have this running by the end of the month and I have other pieces to finish. I'm horrible at IIF statements, so if anyone could help me, I would greatly appreciate it! Thanks so much! Liz |
Thread Tools | |
Display Modes | |
|
|