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

Kamis, 24 Mei 2012

RE: [MS_AccessPros] Finding "This Fiscal Year"

Thanks John,

That works as planned.

Best wishes,

Robin Chapple
At 24/05/2012 06:20 PM, you wrote:
>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@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Thursday, May 24, 2012 9:11 AM
>To: MS_Access_Professionals@yahoogroups.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
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>