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(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
>
>[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