zepold

asked on

We recently started using the ISO 8601 International Standard calendar, but now, my formulas in ACCESS for calculating the week number do not work accurately. I need a formula to calculate the week number using the ISO 8601 standard calendar. I found this formula that works in EXCEL, but does not work in ACCESS:

=INT((NOW()-SUM(MOD(DATE(YEAR(NOW()-MOD(NOW()-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

=INT((NOW()-SUM(MOD(DATE(Y

Last Comment

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

This is the criteria I use in my FISCAL_WEEK field of my query to pull the current week number, (this worked fine when we were going by fiscal week):

<Format(DatePart("ww",Date()),"00")

Basically, I need all the data up to, but not including, the current week.

<Format(DatePart("ww",Date

Basically, I need all the data up to, but not including, the current week.

ASKER

Using the criteria, <Format(DatePart("ww",Date()),"00"), I still get data from this week.

ASKER

I figured it out. I had to define the first day of the week and the first week of the year:

<Format(DatePart("ww",Date(),2,2),"00")

Thank you for your help.

<Format(DatePart("ww",Date

Thank you for your help.

ASKER

Arana confirmed that I was using the correct criteria format, but after more investigating, I figured out that I needed to expand the Date function parameters.

oh sorry if datepart didnt work as expected, the other one sure works but its a little more elaborated (the one that uses floor() ),

I just read that the one with datepart might not give accurate results under certain conditions.

Since you are talking Fiscal Years then it means finances , so you should be careful, read these

http://stackoverflow.com/questions/31560905/incorrect-iso8601-week-number-using-datepart

http://datapigtechnologies.com/blog/index.php/getting-week-number-in-access/

https://support.microsoft.com/en-us/kb/200299

you might encounter a bug, so the best option is to use 2 custom formulas and recreate the one used here:

So the best approach is to use the 2 formulas here: many date formulas

I put here only the needed ones for you to copy and paste in an access vba module

this one ACCURATELY gives result 53 for years with 53 weeks as opposed to the datepart function

that gives 52 as a result

msgbox(Format(DatePart("ww",CDate(31/12/2015),2,2),"00")) = 52 'this is wrong

(test it with MsgBox ISOWeekNum("31/12/2015") = 53 'this is correct

this way you can even use it in a query

SELECT dDate, ISOWeekNum(dDate) FROM MyTable

I just read that the one with datepart might not give accurate results under certain conditions.

Since you are talking Fiscal Years then it means finances , so you should be careful, read these

http://stackoverflow.com/questions/31560905/incorrect-iso8601-week-number-using-datepart

http://datapigtechnologies.com/blog/index.php/getting-week-number-in-access/

https://support.microsoft.com/en-us/kb/200299

you might encounter a bug, so the best option is to use 2 custom formulas and recreate the one used here:

So the best approach is to use the 2 formulas here: many date formulas

I put here only the needed ones for you to copy and paste in an access vba module

this one ACCURATELY gives result 53 for years with 53 weeks as opposed to the datepart function

that gives 52 as a result

msgbox(Format(DatePart("ww

(test it with MsgBox ISOWeekNum("31/12/2015") = 53 'this is correct

```
Option Compare Database
Public Function YearStart(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If
End Function
Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
' = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer
ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is >= NextYearStart
ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart
ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
YearNum = Year(AnyDate) - 1
Case Else
ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
YearNum = Year(AnyDate)
End Select
If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
Format(ISOWeekNum, "00"))
End If
End Function
```

this way you can even use it in a query

SELECT dDate, ISOWeekNum(dDate) FROM MyTable

ASKER

I am not getting the results that I am looking for when I use the function above. We have a calendar database that our finance group maintains and they have it populated up through the end of the year. Using the ISO 8601 standard, they have calculated that 03/02/16 falls on Week 09. I my query, I am looking for all data from Week 01 through the week prior to the current one. When I run my query using either <Format(DatePart("ww",Date(),2,2),"00") or <IsoWeekNum(Date()), I get data that includes week 9. Now, I am thinking that maybe the date function "Date()" is calculating this week as week 10.

Any way to compensate for that?

Any way to compensate for that?

yes isoweeknum("03/02/16")

returns 9, which is correct

so if you just want up to number 8 just

isoweeknum("03/02/16")-1

or

isoweeknum(date()-7)

returns 9, which is correct

so if you just want up to number 8 just

isoweeknum("03/02/16")-1

or

isoweeknum(date()-7)

Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

https://developer.salesforce.com/forums/?id=906F00000008ueCIAQ

its ISO 8601 compliant