By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,931 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,931 IT Pros & Developers. It's quick & easy.

Week number in access reports and query

100+
P: 157
Hello my good it people!

Since new year we have experienced a fault in our diagrams and reports in access. They show that we are in week 4, even when i let the diagram self make the week number (there is no place to define 'usesystem' in diagrams)

Is there a system setting (we use Windows XP SP2 with Access 2007) which covers this?

In some diagrams we only let access show week instead of date. But in other querys we use datepart() to get week number. Datepart i can manually set to datepart("WW";date();0;0) to get the right week. But then i have to update alot of querys and redesign those who use only the built-in function in the diagram.

So, is there a more easy solution to this?
Jan 19 '10 #1

✓ answered by nico5038

Guess the optimal solution should be found "out side" Access in the regional settings.There the week number is however not defined nor adjustable "straight away".
The only option I see there is the choice of a region using the week number as you need it. :-(

Personally I do use my own function to determine the week number (indeed with the Datepart function like: "DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)") .
The Datepart(WW) can give a number 53 on the first days of January and combined with the year function that's introducing a possible erroneous YearWeeknumber, so my function corrects the year when necessary:
Expand|Select|Wrap|Line Numbers
  1. Function fncYearWeekString(dtDateIn As Date) As String
  2. 'Create year - week string for a given date with string format yyyy-ww,
  3. 'for making sure an alphanumeric sort is correct
  4. 'as ww returns no leading zero ! And correcting the year when first or last days are "over the edge"
  5. If IsDate(dtDateIn) Then
  6.     If DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays) < 10 Then
  7.         If Month(dtDateIn) = 12 Then
  8.             ' correction for last week of year when date falls in week of next year
  9.             fncYearWeekString = Year(dtDateIn) + 1 & "0" & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  10.         Else
  11.             fncYearWeekString = Year(dtDateIn) & "0" & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  12.         End If
  13.     Else
  14.         If Month(dtDateIn) = 1 Then
  15.             ' correction for first week of year when date falls in week of previous year
  16.             fncYearWeekString = Year(dtDateIn) - 1 & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  17.         Else
  18.             fncYearWeekString = Year(dtDateIn) & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  19.         End If
  20.     End If
  21. Else
  22.     fncYearWeekString = "*?*"
  23. End If
  24. End Function
  25.  
By preparing a query with this YearWeeknumber I always get a "controlled" value for every date and a predictable result in graphs etc..

Nic;o)

Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
Guess the optimal solution should be found "out side" Access in the regional settings.There the week number is however not defined nor adjustable "straight away".
The only option I see there is the choice of a region using the week number as you need it. :-(

Personally I do use my own function to determine the week number (indeed with the Datepart function like: "DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)") .
The Datepart(WW) can give a number 53 on the first days of January and combined with the year function that's introducing a possible erroneous YearWeeknumber, so my function corrects the year when necessary:
Expand|Select|Wrap|Line Numbers
  1. Function fncYearWeekString(dtDateIn As Date) As String
  2. 'Create year - week string for a given date with string format yyyy-ww,
  3. 'for making sure an alphanumeric sort is correct
  4. 'as ww returns no leading zero ! And correcting the year when first or last days are "over the edge"
  5. If IsDate(dtDateIn) Then
  6.     If DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays) < 10 Then
  7.         If Month(dtDateIn) = 12 Then
  8.             ' correction for last week of year when date falls in week of next year
  9.             fncYearWeekString = Year(dtDateIn) + 1 & "0" & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  10.         Else
  11.             fncYearWeekString = Year(dtDateIn) & "0" & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  12.         End If
  13.     Else
  14.         If Month(dtDateIn) = 1 Then
  15.             ' correction for first week of year when date falls in week of previous year
  16.             fncYearWeekString = Year(dtDateIn) - 1 & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  17.         Else
  18.             fncYearWeekString = Year(dtDateIn) & DatePart("ww", dtDateIn, vbSunday, vbFirstFourDays)
  19.         End If
  20.     End If
  21. Else
  22.     fncYearWeekString = "*?*"
  23. End If
  24. End Function
  25.  
By preparing a query with this YearWeeknumber I always get a "controlled" value for every date and a predictable result in graphs etc..

Nic;o)
Jan 19 '10 #2

100+
P: 157
Ok. I think i will make a code for this so that i can get away with only changing the code in one place if this is a problem in the future also

Thank you!
Jan 20 '10 #3

nico5038
Expert 2.5K+
P: 3,072
See you're learning fast how to code properly :-)

Success with the application !

Nic;o)
Jan 20 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.