Current fiscal year

[broncojim]

How can I use an IIF statement in a query to return the current fiscal year.
The fiscal year begins in October and end in Spetember.

 

[John Spencer MVP]

Perhaps

FiscalYear: Year(DateAdd("m",3,[SomeDateField]))

For October 2008 to September 2009 that should return 2009.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[broncojim]

John,

What I am looking to do is to use the fiscal year for the current date to
subtract the fiscal year for the INSTALLDATE field of an asset. So, it looks
like you have given me the second part of the equation. How would I write
the first part so that today, April 2, 2009, returns 2009 for the current
fiscal year, but if I run the query in the future, say October 1, 2009, it
would return 2010 for the current fiscal year?

Thanks,

Perhaps

FiscalYear: Year(DateAdd("m",3,[SomeDateField]))

For October 2008 to September 2009 that should return 2009.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

How can I use an IIF statement in a query to return the current fiscal year.
The fiscal year begins in October and end in Spetember.

 

[Bob Barrows]

How can I use an IIF statement in a query to return the current
fiscal year. The fiscal year begins in October and end in Spetember.

Even if your fiscal years are "standard", you would do well to create a
FiscalCalendar table to store the fiscal year and period for each
calendar date. At its simplest, it would have three columns:
CalendarDate, FiscalYear and FiscalPeriod. But there's no need to stop
there: you could also store FiscalWeek, FiscalQuarter, etc. and avoid
the need to calculate those values every time you need one in a query
you are creating.

Yes, it's a little extra maintenance, but it need only occur once per
year, and the added simplicity certainly makes that extra maintenance
well worth it IMO.

 

[broncojim]

Bob,

I understand your logic. However, since I am the only Access user at my
work, I would rather have the query calculate the current fiscal year based
on the current date. There will be very few queries performing this
calculation. Is there a formula to return the current fiscal year based on
the current date, possibly involving the Now function?

Thanks

 

[John Spencer MVP]

If the current date is October 1 2009 the expression
Year(DateAdd("m",3,Date()))
will return 2010

More than that I cannot do.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

What I am looking to do is to use the fiscal year for the current date to
subtract the fiscal year for the INSTALLDATE field of an asset. So, it looks
like you have given me the second part of the equation. How would I write
the first part so that today, April 2, 2009, returns 2009 for the current
fiscal year, but if I run the query in the future, say October 1, 2009, it
would return 2010 for the current fiscal year?

Thanks,

Perhaps

FiscalYear: Year(DateAdd("m",3,[SomeDateField]))

For October 2008 to September 2009 that should return 2009.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

How can I use an IIF statement in a query to return the current fiscal year.
The fiscal year begins in October and end in Spetember.

 

[John W. Vinson]

Bob,

I understand your logic. However, since I am the only Access user at my
work, I would rather have the query calculate the current fiscal year based
on the current date. There will be very few queries performing this
calculation. Is there a formula to return the current fiscal year based on
the current date, possibly involving the Now function?

Now() returns the current time to the second and is overkill here.

Just use the Date() function in John Spencer's expression:

FiscalYear: Year(DateAdd("m",3,Date()))

 

[Bob Barrows]

It really depends on how your fiscal calendar is set up. Different
companies have different fiscal calendars. For example, in the company I
work for, the Fiscal year starts either in the last Sunday in June or
the first Sunday in July, depending on when the previous fiscal year
ended. And although they try to conform to the 445445445445 pattern (4
weeks in period 1, 4 in perid 2, 5 in peiod 3, etc.), the accountants
sometimes tweak that pattern to cause the fiscal periods to align better
with the calendar months (they are never perfectly aligned).

So, you say your fiscal year begins in October. What date in October?
Does it always begin on Sunday? Do you always have 52-week fiscal years?
John's solution is assuming your fiscal months exactly align to your
calendar months. Do they? Does your fiscal year always begin on 1-Oct?
If so, use John's solution.

I'm not really sure you do understand my logic. Getting the current
date's fiscal year could be as simple as:

select fiscalyear from fiscalcalendar where CalendarDate = Date()

If you have a table with a date field, you can join it to the
fiscalcalendar table and do things like getting all data within a
particular fiscal year. There are a lot of benefits to creating a
calendar table, fiscal or not .

 

[Bob Barrows]

I am not following this explanation. Could you explain what you mean by
"subtract the fiscal year "? Perhaps if you showed us some sample data
and then showed us what you want the result from the query to look like,
we would understand your requirement better.

John,

What I am looking to do is to use the fiscal year for the current
date to subtract the fiscal year for the INSTALLDATE field of an
asset. So, it looks like you have given me the second part of the
equation. How would I write the first part so that today, April 2,
2009, returns 2009 for the current fiscal year, but if I run the
query in the future, say October 1, 2009, it would return 2010 for
the current fiscal year?

Thanks,

Perhaps

FiscalYear: Year(DateAdd("m",3,[SomeDateField]))

For October 2008 to September 2009 that should return 2009.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

How can I use an IIF statement in a query to return the current
fiscal year. The fiscal year begins in October and end in Spetember.