ACCESS FEED: RE: [MS_AccessPros] Finding "Next Fiscal Year"

Selasa, 14 Mei 2013

RE: [MS_AccessPros] Finding "Next Fiscal Year"

Hi Robin

You might find this function useful. It will return the last day of the
fiscal year containing a given date (defaults to current date). You can
also add an optional offset to move the result forward or backward by a
number of years. Finally, you can specify the last month of the FY. Here
in NZ, most people use 31 March as the EOFY, so my default is 3, but you can
change to 6 that if you wish.

Public Function LastDayOfFiscalYear( _

Optional GivenDate As Variant, _

Optional Offset As Integer, _

Optional LastMonth As Integer = 3 _

) As Date

If Not IsDate(GivenDate) Then GivenDate = Date

LastDayOfFiscalYear = DateSerial( _

Year(DateAdd("m", -LastMonth, GivenDate)) + Offset + 1, _

LastMonth + 1, 0)

End Function

Best wishes,

Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Clive
Sent: Wednesday, 15 May 2013 04:03
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Finding "Next Fiscal Year"

Hi Robin

If you want to use the next fiscal year before
it starts, then use
"30/06/" & Year(DateAdd("m",6,Date()))

Bear in mind that after 30/06/2013, automatically
... ThisFiscalYear will change to 2013
and NextFiscalYear will change to 2014

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca"
<wrmosca@...> wrote:
>
> If Robin's fiscal year is from July to June, he'd be in FY 2013. I have to
deal with that myself. I use a fiscal period table with the year, period and
start and end date in most of my systems.
>
> I did this because we were originally on a 13=period fiscal year which had
a period for every 4 weeks due to trying to sync with pay periods that were
every 2 weeks. It was a nightmare to do calculations on dates without the
table.
>
> Currently, we are on a regular 12-period FY that is based on months and
starts in July. I kept the table as is not only for historical data but just
in case the finance department decides to go back to the 13-period year.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
>
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-6427073088
1E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "D" <embreydl@> wrote:
> >
> > Are you currently in FY2012 or FY2013?
> >
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
wrote:
> > >
> > > Robin-
> > >
> > > Without testing it, I would guess:
> > >
> > > >"30/06/" & Year(DateAdd("m",+6,Date()))
> > >
> > > John Viescas, Author
> > > Microsoft Access 2010 Inside Out
> > > Microsoft Access 2007 Inside Out
> > > Microsoft Access 2003 Inside Out
> > > Building Microsoft Access Applications
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Robin
Chapple
> > > Sent: Monday, May 13, 2013 7:25 PM
> > > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Finding "Next Fiscal Year"
> > >
> > > Our organisation has a fiscal year from 1st July to 30th June.
> > >
> > > This group advised the code to find "This Fiscal Year":
> > >
> > > >"30/06/" & Year(DateAdd("m",-6,Date()))
> > >
> > > How do I change that to find the next fiscal year?
> > >
> > > Many thanks,
> > >
> > > Robin Chapple
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
>

[Non-text portions of this message have been removed]