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 <access_kri@yahoo.com> 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
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
>
>
Tidak ada komentar:
Posting Komentar