Thank you Crystal. I was thinking I was a major dunce for doing it this
way. But the last job I had (now retired) we used a calendar called the
"retail calendar" which is really screwy and not based on any kind of month
at all. Each begins on a Sunday and ends on a Saturday. Some had four
weeks. Some had five weeks. It's basically standard for the retail
industry. Once I had this table and used it for FY and Fiscal Month I found
all other uses for it. Holiday. Etc. Of course the FY never started in
January.
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Friday, May 25, 2012 9:44 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Finding "This Fiscal Year"
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/Cry
stal_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 <mailto:adtp%40airtelmail.in> >
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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
<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]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar