DoodleNoodle said:
Sorry, just to add to this...
For example - 2005/2006. I need week one of the financial year to start with
the first full week of the year, so for 2005/2006 this would mean week one
would start on the 4th April and finish on the 10th April.
I don't want week one to start on the 1st April.
Hope this makes sense. Thanks.
DoodleNoodle said:
I have a query which splits my data up into weeks with the year begining in
January. I have done this using:
DatePart("ww",[EpsEndDate],2,3)
Is there anyway I can get this to run for the financial year - April to
March.
I believe it might be more complicated than some cases...
I would probably set up a table "tblFiscalYear"
with 2 fields
Yr Long
FiscalYearStart Date/Time
enter as many Yr's as appropriate,
then run an update query that calcs
the fiscal year start.....
{limited testing in Immediate Window...}
yr=2005
?DateSerial(yr,4,Choose(WeekDay(DateSerial(yr,4,1)),2,8,7,6,5,4,3))
4/4/2005
yr=2006
?DateSerial(yr,4,Choose(WeekDay(DateSerial(yr,4,1)),2,8,7,6,5,4,3))
4/3/2006
yr=2004
?DateSerial(yr,4,Choose(WeekDay(DateSerial(yr,4,1)),2,8,7,6,5,4,3))
4/5/2004
yr=2007
?DateSerial(yr,4,Choose(WeekDay(DateSerial(yr,4,1)),2,8,7,6,5,4,3))
4/2/2007
then, in my query, for every date field (say "datefield")
in some table (say "sometable"), one could find the
FiscalYearStart in a correlated subquery.....
(SELECT
Max(t.FiscalYearStart)
FROM
tblFiscal As t
WHERE
t.FiscalYearStart <= sometable.datefield) As FYStart
then, within that query (having found "FYStart"),
can you then not compute fiscal week with something
like
DateDiff('w', [FYStart], [datefield], vbMonday) + 1
This might be a case where it pays in the long run
to create a calendar table for all "possible dates"
(using Excel is touted as the easiest route, then import),
then, add fields for FYStart and FYWk
then, run update query to fill in those 2 fields
using above calcs....
truly, this would be more efficient if possible...
