IIF statement for Last Fiscal Year

[Liz]

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

 

[Les]

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.

 

[Liz]

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

 

[Les]

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

 

[Chris Nebinger]

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

 

[Lynn Trapp]

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;