Thanks again John,
I did not understand the use of the expression. I have used your
suggestion and I have another long error which is the second image here:
<http://www.rotary9790.org.au/test/test.asp>http://www.rotary9790.org.au/test/test.asp
Regards,
Robin Chapple
At 27/05/2012 03:26 PM, you wrote:
>Robin-
>
>
>
>Perhaps you misunderstand what A.D.'s expression is doing. It is
>returning the
>fiscal YEAR value. To use it in your test, do:
>
>
>
> >Format(Year(DateAdd("m", - 6, Date())),7,1), "mm/dd/yyyy")
>
>
>
>
>
>John Viescas, author
>
>Microsoft Office Access 2010 Inside Out
>
>Microsoft Office Access 2007 Inside Out
>
>Building Microsoft Access Applications
>
>Microsoft Office Access 2003 Inside Out
>
>SQL Queries for Mere Mortals
>
> <http://www.viescas.com/> http://www.viescas.com/
>
>(Paris, France)
>
>
>
>
>
>
>
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Sunday, May 27, 2012 3:38 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: Re: [MS_AccessPros] Finding "This Fiscal Year"
>
>
>
>
>
>AD,
>
>I have rebuilt my project and started again. Your suggestion gives an
>error message: " Type mismatch in criteria expression". My dates are
>formatted dd/mm/yyy.
>
>Regards,
>Robin Chaple.
>
>At 25/05/2012 02:30 PM, you wrote:
> >Finding The Fiscal Year For A Given Date
> >(A Simple Alternative)
> >============================
> >
> > Prima facie, based upon FiscalEndMonth, the following
> > expression should be able to fetch the fiscal year pertaining to a
> > given date (say Dte):
> >'==============================
> >Year(DateAdd("m", - FiscalEndMonth, Dte))
> >'==============================
> >
> > For example, if the fiscal year starts on 1st July,
> > FiscalEndMonth would be 6. The expression would then become:
> >'====================
> >Year(DateAdd("m", - 6, Dte))
> >'====================
> >
> > Interested members of this forum might like to conduct
> > corroborative tests so as to verify consistent results covering the
> > range of potential dates.
> >
> >Best wishes,
> >A.D. Tejpal
> >------------
> >
> > ----- Original Message -----
> > From: John Viescas
> > To: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> > Sent: Thursday, May 24, 2012 13:50
> > Subject: RE: [MS_AccessPros] Finding "This Fiscal Year"
> >
> >
> > Robin-
> >
> > Your IIf statement should be returning:
> >
> > > July 1, 2011
> >
> > But your saying "after 1/7/2012" gives me a clue. In the absence of any
> > formatting instruction, Access converts any date value using the
> > default Short
> > Date format on your machine. SQL accepts only the US format:
> > mm/dd/yyyy - so
> > your expression is returning:
> >
> > > 1/7/2011
> >
> > .. which will get you all dates after January 7.
> >
> > Do this:
> >
> > >IIf(Month(Date())>=7, Format(DateSerial(Year(Date()),7,1), "mm/dd/yyyy"),
> > Format(DateSerial(Year(Date())-1,7,1), "mm/dd/yyyy"))
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> > --------------------------------
> >
> >
> >
> > 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: Thursday, May 24, 2012 9:11 AM
> > To: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Finding "This Fiscal Year"
> >
> >
> >
> > We have a fiscal year which runs from 1st July to 30th June. I need
> > to find dates that are within "This Fiscal Year".
> >
> > I had a similar need and the group advised this SQL which works for
> > the first requirement.
> >
> >
> >
> >IIf(Month(Date())>=7,DateSerial(Year(Date()),7,1),DateSerial(Year(D
> ate())-1,7,1
> > ))
> >
> > It is currently finding dates after 1/7/2012.
> >
> > What do I need to do to change it?
> >
> > Many thanks,
> >
> > Robin Chapple
> >
> >[Non-text portions of this message have been removed]
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar