Thanks crystal,
I tried your function. It was giving two errors 1) exit sub. I changed to Exit Function and FY was returning as 1899. I changed yourdate to pDate and it was returning correct financial year.
Best Regards,
Kumar
--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> 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@...>
> > > 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
> > > >
> > > >
>
Tidak ada komentar:
Posting Komentar