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(Date())-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
Tidak ada komentar:
Posting Komentar