Hi Robin,
in addition to the excellent advice from John and AD ...
I often use a Dates table to help me ( table with a record for each date). I have uploaded a 2K database with my Dates table to
http://tech.groups.yahoo.com/group/MS_Access_Professionals/files/Crystal/Crystal_Dates__2Kmdb.zip
I added a field for FY. You can use update queries to put the information in. Then, you can also link in the dates table to get FY. You can use the Dates table to create a report that shows every day whether there was activity or not -- and see where stuff is missing. I also use a Dates table when a workday is not midnight to midnight to put in a date/time and find out the workday -- same principal as FY being something other than Jan 1. The advantage of using a table is that you can link fields. The only way to link on an equation is to use it in criteria or edit the ON clause of the SQL statement.
Warm Regards,
Crystal
*
(: have an awesome day :)
*
________________________________
From: A.D. Tejpal <adtp@airtelmail.in>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, May 24, 2012 10:30 PM
Subject: 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]
Tidak ada komentar:
Posting Komentar