ACCESS FEED: Re: [MS_AccessPros] Financial Year and YTD calculation

Jumat, 27 Mei 2011

Re: [MS_AccessPros] Financial Year and YTD calculation

darn side mouse buttons! ... wasn't done...

'~~~~~~~~~~~~~~
Function GetFY(pDate as variant) as variant
GetFY = null
if isnull(pDate) then exit sub
GetFY = year(YourDate - datepart("y",DateSerial(year(YourDate),3,31) )
end function
'~~~~~~~~~~~~~~

in a query:

field --> FY: GetFY([fieldname])

on a form or report:

ControlSource --> =GetFY([controlname])

Warm Regards,
Crystal

Access Basics by Crystal (Bill Mosca's site)
http://thatlldoit.com
Free 100-page book that covers essentials in Access

*
(: have an awesome day :)
*

--- On Fri, 5/27/11, Crystal <strive4peace> wrote:

> Hi Kumar,
>
> you can also do something like this:
>
> '~~~~~~~~~~~~~~
> year(#4/1/2011# -
> datepart("y",#4/1/2011#)+1)
> '~~~~~~~~~~~~~~
>
> I used absolute values from this year, but you can
> construct the equation so it would always work. Since
> your fiscal year starts after the actual year, subtract the
> days difference for the particular year and use the YEAR
> function. simple and effective ;)
>
> Year(DateExpression) --> return the year for the date
> datepart("y",#3/31/2011#) --> returns the day number of
> March 31st (Jan 1 is 1)
>
> so to make this completely variable, you can do something
> like:
>
> '~~~~~~~~~~~~~~
> year(YourDate -
> datepart("y",DateSerial(year(YourDate),3,31) )
> '~~~~~~~~~~~~~~
>
> you could make a function to do this for you:
>
>
> '~~~~~~~~~~~~~~
> Function GetFY(pDate as date) as variant
>
> year(YourDate -
> datepart("y",DateSerial(year(YourDate),3,31) )
> end function
> '~~~~~~~~~~~~~~
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
> --- On Fri, 5/27/11, access_kri wrote:
>
> > From: access_kri <access_kri@yahoo.com>
> > Subject: Re: [MS_AccessPros] Financial Year and YTD
> calculation
> > To: MS_Access_Professionals@yahoogroups.com
> > Date: Friday, May 27, 2011, 10:22 AM
> > Thanks Andy, I tried
> > FY:
> >
> IIf(Month([ENTRYDATE])<4,Year([ENTRYDATE])-1,Year([ENTRYDATE]))
> > in the totals query and it is working fine.
> >
> > Regards,
> > Kumar
> >
> > --- In MS_Access_Professionals@yahoogroups.com,
> > "Andrew Mills" <amills@...> wrote:
> > >
> > > Those dates are absolute. Your calendar year
> > will always be 04/01 - 03/31
> > >
> > >
> > >
> > > Why can't you hard code the start date in your
> "YTD"
> > function as
> > > 04/01/[current year] to CurrentDate?
> > >
> > >
> > >
> > > You have to do something like "SELECT * FROM YTD
> WHERE
> > Date <= CURRENTDATE
> > > AND DATE >= 04/01/2011" to get the values
> > >
> > >
> > >
> > > Thanks
> > >
> > > Andy
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com]
> > On Behalf Of access_kri
> > > Sent: Thursday, May 26, 2011 1:34 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Financial Year and YTD
> > calculation
> > >
> > >
> > >
> > >
> > >
> > > Hi,
> > > Our financial year starts from 1st April to 31
> March.
> > I have a plant
> > > performance table with figures for daily
> production,
> > running hours, downtime
> > > etc. We have to report the YTD (year till date)
> > figures starting from 1st
> > > April. Week1 would start from April 1 to April 7
> etc.
> >
> > >
> > > I have totals query to calculate the totals but
> it
> > gives the total starting
> > > from calendar year. How can I get all the totals
> > financial year and compare
> > > with budgeted figures? Do I have to create a
> function?
> > Please help. Thanks
> > >
> > > Best Regards,
> > > Kumar
> > >
> > >