Kamis, 24 Mei 2012

Re: [MS_AccessPros] Finding "This Fiscal Year"

 

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
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@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

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar