Examples:
'DD/MM/YYYY
"1/1/2009" should give `1`
"31/1/2009" should give `5`
"1/2/2009" should also give `5`
Format("1/2/2009", "ww") returns 6.
So, how can I get the correct result?
views:
169answers:
5oh, I see. Thanks Sosh. It *works* now :-P
Nick D
2009-10-30 13:25:46
@Sosh: I am curious to see what the OP expects for 01/05/09 (Jan 5th 09)? I think OP might expect Jan 5th to be week 1 whereas it will be week 2 as per your example.
shahkalpesh
2009-10-30 13:30:21
There is also a firstweekofyear argument for the format function: vbUseSystem, vbFirstJan1, vbFirstFourDays, vbFirstFullWeek
Remou
2009-10-30 13:34:50
@shahkalpesh, I expect `2` and I get `2`.
Nick D
2009-10-30 13:38:10
@Nick: Sosh is right in that case. The week starts from Monday as per your example.
shahkalpesh
2009-10-30 13:44:58
A:
If sunday is the first day of the week (as it is in some locales) then 6 is the correct weeknumber for "1/2/2009" (february 1. 2009)
Fedearne
2009-10-30 13:18:24
+6
A:
It's doing two things here which don't match your expectations, I think: Assuming you want the week with Jan 1 in as week 1, and using Sunday as first day of the week So it has week 1 running from Sunday 28th December 2008 to Saturday 3rd Jan 2009.
Week 6 would begin on Sunday 1st Feb by this method.
The ISO standard is for week 1 to be the one containing 4 days of January, or the first Thursday of the year (different ways of expressing the same thing). You can specify this method of calculation and the first day of the week:
Format(SomeDate,"ww",vbMonday,vbFirstFourDays)
see here for syntax: http://office.microsoft.com/en-us/access/HA012288391033.aspx
AdamV
2009-10-30 13:24:29
+1, this is the complete answer. I hope you don't mind the cosmetic edit.
Henk Holterman
2009-10-30 13:31:54
Not at all, and I now got round to updating my profile info as well so I have a proper name (I just popped by from superuser.com and signed up to answer this one)
AdamV
2009-10-30 13:38:38
http://www.rondebruin.nl/isodate.htm is another good reference for ISO
Dick Kusleika
2009-10-30 14:02:30
+2
A:
Regardless of the day of the week your week starts on, you need to pass unambiguous date values. "31/1/2009" can only be one date (Jan 31st), but "1/2/2009" could be Jan. 2 (US style) or Feb. 1st (everybody else who has more sense that we USAns).
In this case, I'd use DateSerial() to make sure the date is not misinterpreted:
Format(DateSerial(2009,2,1), "ww", vbMonday)
While this is not causing your problem, because Access helpfully utilizes your system's localized date settings, I think it's something you should do anyway. You certainly are forced to do so in SQL in Access, so I don't think it's a bad habit in code and expressions.
David-W-Fenton
2009-10-30 23:40:25