Can I...

[wh00t]

my works business calendar is split into week numbers

is there an easy way that I can automatically (through an append query) add our given week numbers to dates that exist on tables which get the information from another machine.

i.e.
table with dates

11/10/02
12/10/02
13/10/02

would like query to add
Date - Week
11/10/02 - 24
12/10/02 - 24
13/10/02 - 25

 

[Rich]

Just use the DatePart("ww",MyDate) to return the week

 

[wh00t]

but how do I tell it that week 1 starts on 28/04/02 not on 01/01/02?

 

[Rich]

Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 4, 28), 1, 0)
End Function


Public Function FinancialWeek(dDate As Date) As Integer
FinancialWeek = (Date - DateSerial(FinancialYear(Date), 4, 28)) \ 7
End Function

 

[wh00t]

sorry to be a pain in the arse, but how can I use that through a query?

 

[Rich]

MyYear:FinancialalYear([MyDateField)
MyWeek:FinancialalWeek([MyDateField)